Excel, Paste Link Returning Zero's, need it to show blank cells not empty

Question

Dear All

I'm having trouble with Excel, I have an original table which holds data and does all the calculations. I have created a new work book which holds other data. So I don't have to keep opening all the different work books I have paste linked a table to from the new work book to my original one so in inputs the data. This works fine but anything that has not been filled in has automatically put a Zero as there is no Data in the source cell. This is messing with all the calculations in the original work book.

My question is how do I stop this from happening, is there away I can tell the source cell that it is not empty but blank.

Hope this makes Scenes

Dave

Answer 1

I have two files; whimsically call TheMaster and TheSlave.

When I do I Paste Link from master to slave, I get formulas such as

=[TheMaster.xlsx]Sheet1!B1

I could just have easily typed these in place of using the Paste Link tool

So I can modify one (say the one in E1) to

=IF([TheMaster.xlsx]Sheet1!B1, [TheMaster.xlsx]Sheet1!B1, "")

Then I can copy (or drag, or double click the fill handle) to fill other cells in column E.

Now blank/empty cells display as a blank rather than 0.

Be aware that (suppose E5 looks blank) =ISBLANK(E5) will return FALSE since the cell is not empty (it holds a formula that displays nothing).

However, if you use a formula such as =COUNT(E1:E10) to see how many cells hold numbers, the 'blank' cells will NOT be counted.

Note that in =IF([TheMaster.xlsx]Sheet1!B1,

any non-zero in the B1 cell will be taken to mean TRUE in the first argument, so there is no need to use =IF([TheMaster.xlsx]Sheet1!B1<>0,

However, that syntax may be easier to understand.

Does my suggestion help with  This is messing with all the calculations in the original work book. ?

best wishes