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
1
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"
0
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")
0
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
0