Synchronized drop downs (Excel 2003)

Topic

New Reply

I have a spreadsheet that has synchronized drop downs. They work great. I am supposed to help somebody make changes. I have looked at various very good posts that show how to synchronize drop downs. However, the file I have has no macros, or code on a sheet, yet the synchronization works.
I am attaching a stripped down version of the file.
Thanks for any help!

itconc

Reply | Quote

Replies

The list boxes work by using Data Validation. Select one of the cells in column A and then select Validation from the Data menu. That should show you how that works. The synchronization works by again using Data Validation in the other columns with the INDIRECT and VLOOKUP functions. Select a cell in column B and again select Validation from the Data menu to see this.

Reply | Quote

Thank you – never occured to me to look there.
I really apreciate your help!

Reply | Quote

The dropdowns use the Data | Validation feature in combination with lots of named ranges and the INDIRECT function. It’s probably best explained using an example.

Cell A8 on the Input Sheet has Data | Validation set to allow selection from a list, its source is the fixed range T87:T95.
Let’s say you select Completion Efficiency from the dropdown list.
Cell B8 has Data | Validation set to allow selection from a list too, its source is a formula

=INDIRECT(VLOOKUP($A8,LookupTable1,2,0))

LookupTable1 is a named range created in Insert | Name | Define, it refers to =’InPut Sheet’!$T$87:$X$95.

VLOOKUP($A8,LookupTable1,2,0) looks up the value of A8 (Completion Efficiency) in the first column of LookupTable1 (column T) and returns the corresponding value from the second column (column U). In this example, this is “Subproblem2”. So the INDIRECT function returns the range named Subproblem2. This refers to =’InPut Sheet’!$U$165:$U$175. In other words, the range U165:U175 is the source range for the dropdown in cell B8.

The other dropdowns work similarly.

Reply | Quote

Thanks Hans. I appreciate the explanation.
itconc

Reply | Quote