Data Validation - Indirect function

**Newport Count** · 06-09-2009, 10:27 AM

Hi,

i know it is possible for a selection in a drop down box to determine another field using the INDIRECT function in validation

eg - 1st drop down box - Football, Rugby, Cricket
2nd drop down box (if chosen football) - displays list of football teams
2nd drop down box (if chosen rugby) - displays list of rugbyteams
2nd drop down box (if chosen cricket) - displays list of cricket teams

but is it possible for the 1st drop down box to determine what is available in a range of other drop downs?

eg - 1st drop down box - Football, Rugby, Cricket
2nd drop down box (if chosen football) - displays list of football teams
3rd drop down box (if chosen Man Utd) - displays list of Man Utd players

thanks in advance
Rich

**NBVC** · 06-09-2009, 10:33 AM

Just continue on the same way and make lists of team players and name those lists as per the selections you can make in the 2nd drop down.. using INDIRECT() to get proper list.

http://www.contextures.com/xldataval02.html

**Newport Count** · 06-09-2009, 11:38 AM

Thanks!

but what if the first drop down is eg types of industry

the second drop down is a long list of different companies for each industry

then the third drop down relates to anyone of these companies (basically the third drop down will give the same options no matter which company is chosen)

does that make sense?

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

**Newport Count** · 06-09-2009, 05:06 PM

Thats great - much appreciated!

**NBVC** · 06-09-2009, 05:11 PM

Please remember to mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save