Using IF statement with Data Validation "list" ????

Posted by Rick M on July 09, 2001 7:23 AM

I have a drop down list from Data Validation. Is it possible to incorporate an IF statement to say...if E12=lamp, use list A1:A120 , use list A121:A140.
I am trying to minimize the entries in the list if possible

Posted by Ben O. on July 09, 2001 7:32 AM

Rick,

In any cell (we'll use B1 for this example) enter your IF statement and have it return the range of cells as a text value. So, for your example it would be, =IF(E12="lamp","A1:A120","A1:A140"). Then, for your data validation list formula, refer to the cell with your IF statement like this:

=INDIRECT(B1)

That should work.

-Ben

Posted by Rick M on July 09, 2001 7:44 AM

THANKS BEN -

Posted by Aladin Akyurek on July 09, 2001 9:35 AM

Ben & Rick,

You can avoid that extra step of processing that INDIRECT incurs. Moreover, INDIRECT is a volatile function you should not use unless absolutely necessary. So I'd suggest using directly as source formula in Data Validation:

=IF(E12="lamp",A1:A120,A1:A140)

Aladin

Posted by Rick M on July 09, 2001 9:58 AM

Aladin:
Thank will only work if the list is in the same sheet. In my case the list is in another sheet.
Thanks. Ben & Rick, You can avoid that extra step of processing that INDIRECT incurs. Moreover, INDIRECT is a volatile function you should not use unless absolutely necessary. So I'd suggest using directly as source formula in Data Validation: =IF(E12="lamp",A1:A120,A1:A140) Aladin

Posted by Aladin Akyurek on July 09, 2001 10:23 AM

Rick,

That should not be a problem if the sheets are in the same workbook. just prefix the range with the name of the sheet in which it is contained as in =IF(E2="lamp",Sheet2!A1:A9,Sheet3:B10:B15. Also, you can name them say SourceA and SourceB and use these names in the IF-formula instead.

Aladin