equal result zero or blank

[andreisobaru]

Hi,

So when you put a formula in a cell that it is equal to another:
let's say in A1 you write =A2
and A2 is blank
then in A1 it appears 0
but sometimes, just sometimes, when you equal a cell to a blank cell, the first cell remains blank
what determines this behaviour? I need the cells to remain blank when I equal them to a blank cell
I am not looking for a solution like A1=if(A2=0,"",A2), this is not applicable in my case, I simply want to know what determines for the cell with the formula if it shows 0 or remains blank.

thanks,
Andrei

 

[andreisobaru]

this is correct, thanks a lot, I seem to be getting closer
so you are saying that normally when you put a cell equals a blank cell, the result shows 0, no way to avoid this, right?
but if you put a cell to equal a null text string, it shows that null text string
then, do you know other ways to generate null text strings, other than to put a formula like ="", and then copy paste values?
and except null text strings, is there any other situation where a cell would look as if it is empty, but actually it wasn't?

 

[Jonmo1]

Honestly....

You're 'creating' a situation that most people try to Avoid at all costs.
In the long run, you're far better off letting your blanks be TRUELY blank, and just use
=IF(A2="","",A2)

It will save you many a headache later on.

 

[andreisobaru]

I know this would be better
Unfortunately I am working with a spreadsheet with very many formulas and links to other workbooks
it is unpractical to edit every formula and wrap it into =IF(="","",[formula]) and one client wants too see blanks, where in the other (linked) workbooks it is blank but in this "centraliser" file, it shows 0 instead of blanks, so this is causing some insatisfaction[/COLOR] :nya: I am trying to find a solution for this

 

[Jonmo1]

Is it just a matter of getting the results consistent?
Then your better bet is to correct the 'false blanks', the ones that came from a formula returning "" that was pasted as values.

Here's a code you can use that will go through the whole workbook, and change those false blanks to true blanks.

Save a copy of your book first.

Sub test()
Dim PrevCalc As Variant, ws As Worksheet, c As Range

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    PrevCalc = .Calculation
    .Calculation = xlCalculationManual
End With

For Each ws In Worksheets
    For Each c In ws.UsedRange.SpecialCells(xlCellTypeConstants)
        If Len(c) = 0 Then c.Value = c.Value
    Next c
Next ws

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = PrevCalc
End With
End Sub

 

[andreisobaru]

thanks for your help, but if I convert the false blanks into true blanks, then the links to them would show 0, which is inconvenient
let me go through the whole situation again:
I have cca 90 individual workbooks (excel files)
some cells in these workbooks have numbers in them, others are blank
I also have one centraliser taking results from all the 90 files
but when the centraliser points (equals) to a blank cell in an individual file, it shows 0.
this is unwanted. It should show also a blank, not 0

 

[Jonmo1]

let me go through the whole situation again:

Not necessary, the situation is clearly explained already.

but when the centraliser points (equals) to a blank cell in an individual file, it shows 0.
this is unwanted. It should show also a blank, not 0

Unfortunately, the solution to that is to use
=IF(A2="","",A2)
OR
If you just don't want to SEE the 0's, go into the Excel Options and UNcheck "show zero values'

 

[Jonmo1]

If you absolutely MUST convert True Blanks to Null Text Strings, try this
Though I HIGHLY recommend NOT doing this, you'll probably end up regretting it later.

Press CTRL+A
Press CTRL+G
Click Special
Select Blanks
Click OK
Press '
Press CTRL + ENTER


You'll have to repeat that on each sheet.
You can probably record a macro to do it for you.

 

[andreisobaru]

yes, I see that there seems like there isn't a simple solution for my problem
since there are links to 90 files, and around 50 links to each file, it would mean to edit 4,500 formulas, this is why I said it is impractical
and I cannot drag the formula on column because each row points to a different file
also I cannot drag the formula on row because there are differences on how it points to each file
re: excel options - the idea is not just for me not to see zero values, but for anyone who opens the workbook, regardless of their excel options
at the moment I think that I have to go towards converting the true blanks in individual files into false blanks
but thanks a lot for your help, you made me realise what was going on

 

[Jonmo1]

re: excel options - the idea is not just for me not to see zero values, but for anyone who opens the workbook, regardless of their excel options

That setting follows the book, it's not dependant on who opens the book.

 

[andreisobaru]

yes it does!
that's right! - this might just solve the problem
thanks a lot again - I think you provided me with every info I needed to manage this situation

Andrei