Request of support about "False" Empty Cells

Occasional Contributor

Dear All,

I have a trouble when I make a copy value from a Pivot Table. Where cells in Pivot table are empty, excel does not recognize those cell as empty ones. So when I apply a formula witch involve those cells, an error occurs.

 

A simple Vlookup pointing that cells, as I expect, should return a "0" value, but instead of "0" it show me an empty cell, like if there is some value in it.

 

Does anyone have any idea about it? I use large excel files and sometimes this cells are really too much to be corrected manually.

 

Thank you all!

Marco

7 Replies
Edit. The problem occurs when I use a formula like this: =IF(AL4+1=1;"";AL4). When Excel applies "", i cannot use it in any formula without an error...
Hello Macro!!
If you use reference of cell containing above formula, you can have two scenarios:
1. You use above cell reference (say A1) with a number like B1= A1*10, you will get error because storage value is space (assuming TRUE case in A1) and it can't be multiplied with a number.
2. You use above cell reference (say A1) with a text like =CONCATENATE("Am",A1,"Co"), you will get result 'Am Co'.

So if you want to use A1 i.e " " then you may use IFERROR formula to get result without error.

Hope I understood your problem correctly.

@amands_1,

first of all thank your for your reply.

Unfortunately I do not use spaces between the " symbol, because I just want an epty cell if there is no value inside.

In general, if I use an =iferror(vlookup(A1;B1:C10;2;FALSE);""). This formula show me a value if present in that range, otherwise put nothing, neither a "0" value. The problem is starting from this point. If I make a copy value of the cell "A1" when it is empty, and try to use it inside a formula, excel show me a #value! error.

If I try the =Code(A1) it show me that it is an empty cell, but in reality Excel use it like a non-empty one...

 

Thanks

Marco

@Marco853 Not entirely sure what you are trying to do, but a cell with nothing in it is considered to be blank/empty. However, a cell with "" may appear to be blank/empty for the human eye, but it contains an empty string and for Excel, the cell is not blank/empty.

Can you share a file or include a screenshot showing the formula you are using and the content of the cells/range?

Thank you Riny,
It's exactly what I suspected. I have a database and I create a Pivot starting from that DB. On another file I have a series of vlookup pointing that Pivot but, as the file is really big, I don't want to see an "ocean of zeroes" when I do not have any data for a specific cells. So I use the formula I wrote on my previous post (=iferror(vlookup(.....);""). This is ok but when I link formulas on this file appears an "ocean of #VALUE!" because of the empty string... I really don't know how to manage this problem...

@Marco853 Still don't understand what you are trying to achieve, but if having a lot of zeroes is the problem, consider a custom format that suppresses zeroes. Something like General;General;

File works in this manner:
DB --> Pivot --> link the Pivot by vlookup to a list of data --> I apply a formula starting this list.
it's easier to view data compiled if all cells around it are blank, instead of looking for a number when i see zeroes all around, so I use a iferror(vlookup(...);"". And from this "" start the problem as far as I've understood. if I use a iferror(vlookup(...);0) everything works fine but it's not easy to show an read. I hope it's more clear now