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
You might try using a Lookup function.
In case you are not familiar with it:
Go to Sheet2.
Fill cells A1 through A4 with the list of suburbs.
Fill cells B1 through B4 with the list of corresponding regions.
Go to Sheet1
In cell B1 type (without the quotes) “=IF(ISBLANK(A1), “”, LOOKUP(A1, Sheet2.$A$1:Sheet2.$A$4, Sheet2.$B$1:Sheet2.$B$4))”
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)”
Copy cell B1 down however far you want it to go.
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.