Empty Cells
 

When I do a lookup for a specific content of an array of cells that includes
blank cells I get different results if the blank cells have been cleared by
setting them to "" vs cells that I have manually done a "Clear All" command.
These cells are somehow different to the formula
VLOOKUP($C37,Data!$A$5002:$N$9999,$GE37,FALSE), when $C37 is the blank cell
and $GE37 is any number. In the case of the $C37 being cleared with "", I get
a #Value result. In the case where $C37 has been manually cleared with a
"Clear All" command I get an "ISNA" result.

Is there a way with a Macro to clear all blank cells so they give the save
results from the lookup equation.

Empty Cells
 

Hi,

1. There is nothing in your formula that would cause Excel to return ISNA?
What is in your lookup table for a blank cell? What do you want Excel to do
if the cell is "" verses blank?

You might just write

=IF($C37="","",VLOOKUP($C37,Data!$A$5002:$N$9999,$ GE37,FALSE))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"jswalsh33" wrote:

When I do a lookup for a specific content of an array of cells that includes
blank cells I get different results if the blank cells have been cleared by
setting them to "" vs cells that I have manually done a "Clear All" command.
These cells are somehow different to the formula
VLOOKUP($C37,Data!$A$5002:$N$9999,$GE37,FALSE), when $C37 is the blank cell
and $GE37 is any number. In the case of the $C37 being cleared with "", I get
a #Value result. In the case where $C37 has been manually cleared with a
"Clear All" command I get an "ISNA" result.

Is there a way with a Macro to clear all blank cells so they give the save
results from the lookup equation.

Empty Cells
 

Your answers allows me to avoid the problem. I would be interested to know
whether clearing the source cell is cleared by entering "" is different
from manually clearing a cell with "Clear All". If so is there a macro that
is the equivalent of "Clear All."

The blank cells in the source cell are created with the formula
IF('C:\SST\[Yellows030309.xls]Yellow
(15)'!$B$30="","",'C:\SST\[Yellows030309.xls]Yellow (15)'!$B$30).

The resulting cell with the "" entry behaves differently than does a cell
that has been cleared with "Clear All"

Thanks for you help.

"Shane Devenshire" wrote:

Hi,

1. There is nothing in your formula that would cause Excel to return ISNA?
What is in your lookup table for a blank cell? What do you want Excel to do
if the cell is "" verses blank?

You might just write

=IF($C37="","",VLOOKUP($C37,Data!$A$5002:$N$9999,$ GE37,FALSE))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"jswalsh33" wrote:

When I do a lookup for a specific content of an array of cells that includes
blank cells I get different results if the blank cells have been cleared by
setting them to "" vs cells that I have manually done a "Clear All" command.
These cells are somehow different to the formula
VLOOKUP($C37,Data!$A$5002:$N$9999,$GE37,FALSE), when $C37 is the blank cell
and $GE37 is any number. In the case of the $C37 being cleared with "", I get
a #Value result. In the case where $C37 has been manually cleared with a
"Clear All" command I get an "ISNA" result.

Is there a way with a Macro to clear all blank cells so they give the save
results from the lookup equation.