If statements in Data Validation

[lost and confused in excel-land]

I have a vlookup statement in cell c7 that returns a value based on a drop
down list in C6.

I have other dropdown lists below C6 (D6, E6, F6...) that allow various
choices. HOWEVER - they should not even appear if C7 returns a 0.

I wanted to put in an IF statement in the data validation for cells below C6
that say =IF(c7>0,=$B$399:$B$400,"") I keep getting an error. I think it
has something to do with the =

I tried:

=if(c7>0,vlookup($B$399:$B$400," ")) and it doesn't want to work either.


Many, MANY thanks !!!

 

[Tim Zych]

Data validation should return a True or False value.

I've looked at that formula and can't figure out what you want to do, but it
is clear it is not returning a true or false value.

When doing custom data validation, it can be helpful to construct the
formula in the worksheet so that you can see it and debug it easily. Then
when it's working copy it to the data validation dialog.

--
Tim Zych
www.higherdata.com
Compare data in Excel and find differences with Workbook Compare
A free, powerful, flexible Excel utility


"lost and confused in excel-land"

 

[John C]

Kind of ugly.....

Assuming the ranges you are having in the drop down lists are named, (i.e.:
through Insert-->Name-->Define, etc.), you could have a NullRange defined (a
single absolute cell with no data, i.e.: $IV$65536 named Null Range (don't
put it on your sheet, just use the insert name define).

Then in the cells that have the drop down boxes, you can have
Data-->Validation, choose list, and your source could be as follows:

=INDIRECT(IF(C7=0,"NullRange","")&IF(C7<>0,"TheRange",""))

 

[Tim Zych]

After seeing John's answer I see what you want to do now. Not sure why I
thought you were trying to do a custom formula.

Another way for the validation (list).

=IF(C7>0,B399:B400,INDIRECT(ADDRESS(ROW(),COLUMN())))

If the criteria is not met, the list is itself (a single cell). Pretty cool
how Excel lets you perform self-referencing in validation.


--
Tim Zych
www.higherdata.com
Compare data in Excel and find differences with Workbook Compare
A free, powerful, flexible Excel utility

 

[T. Valko]

=IF(c7>0,=$B$399:$B$400,"")

Try this:

=IF(C7>0,$B$399:$B$400)

Note that the drop arrow will still display but you won't be able to make a
selection if C7>0 = FALSE.

--
Biff
Microsoft Excel MVP


"lost and confused in excel-land"

 

[Tim Zych]

I tried this but it didn't let me enter the validation formula if C7
evaluated to False. The error was: "The list source must be a delimited
list, or reference to single row or column".

Enters fine when C7 evaluates to True.

I like this one the best.

 

[lost and confused in excel-land]

Everyone has been most helpful...I changed the structure of the file and was
able to accomplish what I needed to do. I appreciate the help. Still
learning, and asking plenty of questions...thanks for the tips and patience.

 

[T. Valko]

Another way:

=IF(C7>0,$B$399:$B$400,NA())

If you get the message: The source currenty evaluates to an error........

Just answer YES.