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

You may not use reference operators for data validation criteria
Display two columns in data validation list but return only one - Guide
Could not complete this operation. there was not enough memory available - Guide
Tmobile data check - Guide
Gta 5 data download for pc - Download - Action and adventure
Windows10upgrade9252.exe is not a valid win32 - Guide

1 response

Answer 1 / 1

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

Pedro
Jun 6, 2012 at 05:49 AM

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"

Pedro
Jun 6, 2012 at 06:58 AM

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 555
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

Similar discussions

How To Fix Mint Mobile Data Not Working?

Unlock safe folder without losing any data

Excel - IF function with multiple words

Similar discussions

Subscribe To Our Newsletter!