DATA VALIDATION LIST- IF true, Different List if False???

[tarynmahon]

Im wondering if there is anyway I can have more than one data validation list assigned to one cell based on whether an IF formula is true or false? eg.

COL A, COL B,

LIS, (Data Validation dropdown list)

SDL, (Data Validation dropdown list)

AQ, (Data Validation dropdown list)


There will be 3 lists of data elsewhere on the page that has names, I would like to have the dropdown box display the list of data that is relevant to column A


Is this possible please?

 

[keymaster]

You can do this with custom data validation. See here for an example:


http://chandoo.org/wp/2008/11/25/advanced-data-validation-techniques-in-excel-spreadcheats/

 

[tarynmahon]

Thats sort of what I need although I have 3 instances, would I need to use a SUMPRODUCT?

 

[keymaster]

What do you mean by 3 instances? Do you need this validation in 3 rows? If so, just apply for one and then copy validations (copy, paste special > validations)

 

[tarynmahon]

No, I need 3 different lists of information, sorry my post title wasnt very helpful.

to clarify;

IF A1= X1 Return X2:X50

IF A1= Y1 Return Y2:Y50

IF A1= Z1 Return Z2:Z50, IF NOT RETURN ERROR


I hope that helps

 

[keymaster]

No problem. Follow below instructions.


Select B1, go to data validation > list

In the list area,

write =IF($A1=$X$1,$X$2:$X$50,if($A1=$Y$1,$y$2:$y$50,if($A1=$z$1,$z$2:$z$50,false)))

Click ok.


This should work.

 

[SirJB7]

Hi, tarynmahon!

Gived a look to this link yet?:

http://chandoo.org/forums/topic/data-validation-list-automatically-shows-the-first-item-in-the-list

Regards!