Return Blank

B

Brad

Thanks for taking the time to read my question.

I have a column of formulas like this:

=if(B1="","",B1)

This works fine, but I want to use this column as the row source for a value
list from Data - Validation. There is a check box there that says Exclude
Blanks. "" is a zero length string, not blank, so my value lists are long and
empty.

How can I make my formula return nothing instead of a zero length string?

Thanks,

Brad
 
B

Biff

Hi!
There is a check box there that says Exclude
Blanks. "" is a zero length string, not blank,
so my value lists are long and empty.
Click to expand...

Actually, it says Ignore blank but that's not what it's for. It doesn't mean
to exclude from the list any blank or empty cells.
How can I make my formula return nothing instead of a zero length string?
Click to expand...

You can't.

The only way to get rid of the blanks in your drop down list is to create a
source that doesn't contain any blanks.

Biff
 
B

Biff

What is the Ignore Blank for then?

An example:

You want users to enter a value >=10 in cell B1 if cell A1 = 0.

Select cell B1
Goto Data>Validation
Select Custom
Formula: =AND(A1=0,B1>=10)
OK

Leave cell A1 empty. An empty cell will evaluate to zero.

Enter 1 in cell B1. It allows you to do so but 1 does not meet the criteria
specified. A1 being empty evaluates to 0 but B1 is not >=10.

Now goto Data>Validation and uncheck Ignore blank>OK.

Try entering a 1 in cell A1.

Biff
 
B

Brad

Very very cool!

Thanks Biff.

Brad

Biff said:
An example:

You want users to enter a value >=10 in cell B1 if cell A1 = 0.

Select cell B1
Goto Data>Validation
Select Custom
Formula: =AND(A1=0,B1>=10)
OK

Leave cell A1 empty. An empty cell will evaluate to zero.

Enter 1 in cell B1. It allows you to do so but 1 does not meet the criteria
specified. A1 being empty evaluates to 0 but B1 is not >=10.

Now goto Data>Validation and uncheck Ignore blank>OK.

Try entering a 1 in cell A1.

Biff
Click to expand...
 
Top