Data Validation - Indirect function

**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")