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

**westbay** · 11-20-2011, 09:52 PM

Hi people,

I am using Excel 07.

I understand the dropdown list is created using the data validation function and can be dynamically linked using some MATCH and OFFSET formula. I am not too familiar with that.

How can I create a dropdown list as below when I select Type A, B etc, the corresponding No. and Amount is also shown correctly?

Type No. Amount.
A 1 3
B 2 4
C 3 5
D 4 5

Thank you.

**scottylad2** · 11-21-2011, 12:39 AM

Perhabs a vlookup would suit you without the need for a dependent drop down list. Look for some post on Vlookup, theres lots on this board. Doing this from my phone so not able to show you a sample at this time but if no one else responds I'll add one later

**foxguy** · 11-21-2011, 12:55 AM

If you want all 3 columns to show in a drop down list:
create a 4th column =A1&B1&B3 and use that column for the validation list.

**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.