Data Validation - Indirect function

**NBVC** · 06-09-2009, 11:49 AM

So let's say Microsoft is one of those companies in list 2...

You would have a list of employees of Microsoft somewhere in your workbook and that list would be named Microsoft...

Then in the Data Validation for the 3rd list would be =Indirect(A2) where A2 contains your second list where you chose Microsoft....

Does that make sense?

If not, please post an actual sample workbook explaining the dilemma.

**Newport Count** · 06-09-2009, 12:33 PM

ok

so list 1 contains different types of industry

list 2 contains actual company names relating to the choice made in list 1

list 3 needs to contain the same options for any of the companies selected in list 2...the selections here would only be different depending on the type of industry chosen in list 1..... they would be the same no matter what company was chosen...so it relates more to list 1 really

eg
list 1 - building, roofing, joinery
list 2 - building - altd bltd cltd.....roofing - dltd eltd fltd.....joinery - gltd hltd iltd
list 3 - for all building companies (altd, bltd, cltd) choices are x,y,z
for all roofing companies (dltd, eltd, fltd) choices are u,v,w
for all joinery companies (gltd, hltd, iltd) choices are r,s,t

make more sense?

**NBVC** · 06-09-2009, 01:22 PM

Attached is a workbook with a couple of options...

Option 1 in Sheet1:

Name only the Industry types as before..

...then for 3rd drop down use formula : =OFFSET(INDIRECT(A1),0,1,,)

This assumes you have the same amount of choices as company names or you will have blanks at the bottom of the dropdown lists.

Option 2 in Sheet2:

Name Industry types as before.. and then name the options similarly but add a suffix, for example, Building_Choices, Roofery_Choices, Joinery_Choices

And then use data validation formula: =INDIRECT(A1&"_Choices")