# Data validation and If statement

Closed
Pedro - Jun 6, 2012 at 04:59 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 11, 2012 at 09:40 AM
Hello,

I have an issue trying to match If statements with data validation.

I have a cell (A1) with a certain result calculated (e.g.: G0,G1,G2).
In another sheet I have a series of lists created (listG0, listG1, listG2)
I want to create another cell (A2) which is dependent on cell A1 but with options to choose from. something like this:

A2=If(A1=G0;listG0);If(A1=G1;listG1);If(A1=G1;listG2)

I tried and it does not work as an input in the data validation box but it works outside...

Also even if it works and I want to display something if A1 is different from anything in the if statement should i put another parameter in all the if statements?
A2=If(A1=G0;listG0;"NA");If(A1=G1;listG1;"NA");If(A1=G1;listG2;"NA") ????

I know this would be easy if excel had something like an elseif command but it doesnt right?

Cheers

## 1 response

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jun 6, 2012 at 05:27 AM
YOU NEED NOT WORRY WHAT IS IN ANOTHER SHEET

suppose in sheets1 in A1 you have either G0 or G1 or G2

just type in A2 type this formula

="LIST"&A1

and enter

now enter any value in A1 and see what happens in A23
thank you. Thats not the point though.
I does not accept the formula inside data validation...
The exact formula I've tried is:
=IF(\$F\$12="1-2 steps";Steps_G25);IF(\$F\$12="2-3 steps";Steps_G25)
It says: "You may not use use reference operators or array constrants for Data Validation criteria"
Ok I manage more or less by entering the formula bellow:

=IF(\$C\$4=Lists!\$G\$22;Lists!\$I\$5:\$I\$6;IF(\$C\$4=Lists!\$G\$23;Lists!\$I\$5:\$I\$6;IF(\$C\$4=Lists!\$G\$39;Lists!\$I\$4:\$I\$5;IF(\$C\$4=Lists!\$G\$4:\$G\$52;Lists!\$G\$22:\$G\$31;"NA"))))

The issue was in the parenthesis. The next if needs to go inside the previous if statement.
BUT the last if doesnt work yet:: what I want is to say again is elseif(\$C\$4="NA")
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jun 11, 2012 at 09:40 AM
Hi Pedro,

Why don't you refer to a cell containing the text "NA", so that when the conditions are not met, only "NA" will be able to be seletected from dropdownlist?

Best regards,
Trowa