Ask Your Question

How do I create a formula referencing information from another sheet?

asked 2015-01-09 07:58:31 +0200

Hails gravatar image

On sheet two I have two lists. One list of all suburbs and the second list with the region to which each suburb belongs under.

How do I create a formula to ensure that if one of team members type the suburb name, that the region allocated to that suburb will appear in the neighbouring columns on Sheet 1?

Thank you

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2016-04-04 03:32:01 +0200

meolar gravatar image

You might try using a Lookup function.

In case you are not familiar with it:

  1. Go to Sheet2.
  2. Fill cells A1 through A4 with the list of suburbs.
  3. Fill cells B1 through B4 with the list of corresponding regions.

  4. Go to Sheet1

  5. In cell B1 type (without the quotes) "=IF(ISBLANK(A1), "", LOOKUP(A1, Sheet2.$A$1:Sheet2.$A$4, Sheet2.$B$1:Sheet2.$B$4))"

  6. NOTE: The IF and ISBLANK parts are not necessary. They just make things look nicer. You could just type (again, without the quotes) "=LOOKUP(A1, Sheet2.$A$1:Sheet2.$A$4, Sheet2.$B$1:Sheet2.$B$4)"

  7. Copy cell B1 down however far you want it to go.

  8. Type a subdivision name into cell A1. Cell B1 should display the appropriate region listed on Sheet2.

If you add rows to Sheet2, you will need to update the formulas in column B on Sheet1.

I hope that helps.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools



Asked: 2015-01-09 07:58:31 +0200

Seen: 111 times

Last updated: Apr 04 '16