Too much IF in Data Validation List Source

**Afrikite** · 04-08-2014, 05:08 PM

Hi,

I try to find a solution (with or without macro) to assign different ranges named "Failure_C..." in a data validation source depending on the value of another data validation list (32 values called)

I started in the Data Validation List Source to fill in IF function but 32 inputs are not accepted and too much caracters in the source.

The formula is starting like that: =IF(A57=$M$78,Failure_C01,IF(A57=$M$79,Failure_C02,IF(A57=$M$80,Failure_C03,IF(A57=$M$81,Failure_C04,IF(A57=$M$82,Failure_C05,IF(A57=$M$83,Failure_C06,IF(A57=$M$84,Failure_C07............

Can you help me ?

Thanks.

**alexzender342** · 04-09-2014, 08:35 AM

em also facing problems

**Duoae** · 04-09-2014, 10:21 AM

Can you just use an OR function instead?

i.e.

=IF(OR(A57=$M$78,A57=$M$79, etc. etc.),"Failure_C01", FALSE)

It's strange though as Excel 2007 should accept longer functions like this one. Is the source file saved in the latest data format or an old one like 97-2003?

[edit] Sorry, ignore me. Was reading more than one thing at a time.

It should work by writing the formula into a cell and then referring to that cell instead of typing the formula into the data validation window?

e.g. in the data validation you write =$A3 and in A3 you write your formula as above and it should work.