Data Validation Multi-column Dropdown

[Guest]

I have a list of items in data validation in column A.

Data validation for column B changes depending upon the value selected in
column A. The dta validation formula I use is...

=IF(A7="Dining Room",DR,IF(A7="Living
Room",LR,IF(A7="Kitchen",KT,IF(A7="Master Bedroom",MBR,IF(A7="Guest Bedroom
1",GB1, IF(A7="Guest Bedroom 2",GB2, IF(A7="Guest Bedroom 3",GB3,IF(A7="Guest
Bedroom 4",GB4,""))))))))

....where DR, LR, KT, etc. are named ranges of Options for each of those rooms.

The problem is I am limited to the number of characters I can have in the
source field of data validation.

How can I get around this? I understand I cannot employ functions, only
named ranges, so it appears a macro or Vlookup function will not work.

 

[Debra Dalgleish]

You can create dependent data validation lists. There are instructions here:

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

 

[Arvi Laanemets]

Hi

=CHOOSE(MATCH(A7,{"Dining Room";"Living Room";"Kitchen";"Master
Bedroom";"Guest Bedroom1";"Guest Bedroom2";"Guest Bedroom3";"Guest
Bedroom4"},0),DR,LR,KT,MBR,GB1,GB2,GB3,GB4)

You can have up to 24 diferent options.


Arvi Laanemets