How to generate a dropdown list that links to other row data?

**westbay** · 11-21-2011, 09:36 AM

I managed to dig a thread on this and one forum member came up with this file as attached.

a) Is there a simpler way than to use the formula below?

=IF(ISERROR(INDEX(Sheet1!$C$2:$C$7,SMALL(IF(Sheet1!$B$2:$B$7=Sheet2!$A$2,ROW(Sheet1!$B$2:$B$7)),ROW(1:1))-1)),"",INDEX(Sheet1!$C$2:$C$7,SMALL(IF(Sheet1!$B$2:$B$7=Sheet2!$A$2,ROW(Sheet1!$B$2:$B$7)),ROW(1:1))-1))

b) I also noticed there is a {} when you click on the cell. However it disappears when you double click it. Why so?

c) What should the formula be, for population, if we stick to the complex formula above?

Thank you.

**foxguy** · 11-21-2011, 03:11 PM

I haven't looked at the file. Just commenting on the {}.

{} indicates that it's an array formula. Array formulas work on a range of cells instead of just one cell. When you double click, you're editing the formula. You save the changes with a Shift+Ctrl+Enter, not just an Enter.

**foxguy** · 11-21-2011, 03:48 PM

Here's a file with simpler formulas.

**westbay** · 11-21-2011, 11:06 PM

Thank you foxguy for the file.

If there are additional rows under the same county, how do I update the formula =IF(ROW()-1>$G$1,"",MATCH($A$2,OFFSET(County,N($H1),0),0)) so that I can display all the rows correctly?

The earlier file provided by you seems to have an error in displaying the additional rows.

**foxguy** · 11-21-2011, 11:43 PM

If you're saying that you want more Names, Countie, Town and Population:

Change the Name "County" to include the new Rows. I used your Range Names as is, so just change the Range Names and the formulas should automatically accommodate them.

If you're going to be constantly adding new Names, Counties, etc., you should look at Dynamic Range Names.
Range Name "County": =Offset($B$1,1,0,CountA($B:$B)-1,1) will automatically increase the size of the Range Name when you add names to the bottom of the list (as long as there is nothing else in column B).

If you're saying that there may be more than 9 names in Sussex County, then just copy the formulas in H:K down as far as you want.