Return Blank
 

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

Return Blank
 

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.

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?

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

"Brad" wrote in message
...
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

Return Blank
 

Sorry for the miss quote.

What is the Ignore Blank for then?

Brad

"Biff" wrote:

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.

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?

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

"Brad" wrote in message
...
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

Return Blank
 

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 DataValidation
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 DataValidation and uncheck Ignore blankOK.

Try entering a 1 in cell A1.

Biff

"Brad" wrote in message
...
Sorry for the miss quote.

What is the Ignore Blank for then?

Brad

"Biff" wrote:

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.

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?

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

"Brad" wrote in message
...
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

Return Blank
 

Very very cool!

Thanks Biff.

Brad

"Biff" wrote:

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 DataValidation
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 DataValidation and uncheck Ignore blankOK.

Try entering a 1 in cell A1.

Biff

"Brad" wrote in message
...
Sorry for the miss quote.

What is the Ignore Blank for then?

Brad

"Biff" wrote:

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.

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?

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

"Brad" wrote in message
...
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