How to paste links without getting zeroes in destination cells when source cells are blank?

[2davidc8]

What I would like to do is select certain rows, not necessarily contiguous, and paste links to them elsewhere, either in the same worksheet or another worksheet in the same workbook.
Some cells in the source rows, however, may be blank. After I do the "Paste Link ...", cells in the destination rows that correspond to the blank cells in the source rows have a zero in them, whereas the source cell had a blank. This is very annoying.
Can someone tell me what is the workaround?

 

[Yongle]

However annoying it is, this is consistent Excel behaviour
Same as with formula =A1 whether A1 is blank or zero the formula returns 0
You can suppress a 0 in any cell with right-click \ format cells \ custom \Type: 0;;;@
Which also (of course) suppresses genuine zeros
Or you can use conditional formatting to format cells with value = 0 in any way you want

 

[Yongle]

Does SOURCEcell contain a GENUINE zero or is it an empty string?

Should genuine zeros be displayed and "blank cells" be supressed?

Workaround to distinguish between them
- enter a formula in DESTINATIONcell instead of PasteLink

Which formula?
- is dependent on what type of data you expect the SOURCEcell to contain when not blank
- should it contain a number?
- should it contain text?

Returns the value of A1 if it contains TEXT otherwise returns an empty string
=T(A1)

Return the value of A1 if it contains a NUMBER otherwise returns an empty string
=IF(ISNUMBER(A1),A1,"")

 

[2davidc8]

@Yongle
Thank you for your help. Here are some clarifications:
1. Because I'm copying entire rows, some cells contain data, others are blank. By blank, I mean they don't contain anything at all. It's simply that nothing has been entered into them yet.
2. I want to copy the link to the source cell and not just use =IF(ISNUMBER(A1),A1,"") because if the value in the source cell changes, I want the new value automatically reflected in the corresponding destination cell.
3. I want the destination cell to exactly reflect what's in the source cell. So, if the source cell is blank, the destination cell should also be blank, and not contain a zero.
Once again, thanks for your help. I've been trying all sorts of things and nothing's worked so far.
Your conditional formatting suggestion might do the trick (haven't tried it yet), but that means I'll have to pre-format every destination cell. Do you see a better solution?
Thanks!

 

[JaccoVis90]

With the function =IF(ISNUMBER(A1),A1,"") or =IF(A1="";"";A1) the new value will automaticly be changed when the source cell is changed

 

[Yongle]

I apologise if I did not make things clear for you.

The formulas I gave go in the DESTINATION cells (where PasteLink would have returned your unwanted zero)
They replace PasteLink.
The formulas should be pointed at the cell you would have linked to.


So instead of PasteLinking to return the value of the cell you are (doing it the old fashioned way and) using a formula to return the value of the cell. A formula allows for flexibility so that you get it delivered the way you want.

anyway - test it and let us know if we can be of further help

 

[2davidc8]

No need to apologize, @Yongle. Thanks @JaccoVis90. I understand what you both are saying.
There is an additional twist. I mentioned that the source rows are not contiguous. But I neglected to mention that the destination rows should be contiguous.
So, for example, I want to copy rows 1, 4, 5 and 10 into rows 301, 302, 303, and 304.
Then, if I change the value in any cell of row 4, for example, the change should be immediately reflected in the corresponding cell in row 302. PasteLink works except for the problem of the zeroes.
Since I will be treating hundreds of rows in this manner, it would be very tedious to figure out which destination row goes with which source row. Basically, what I'm trying to do is to create in the destination area a smaller table consisting of selected rows from the source area, but have the rows linked such that a change in the source row is reflected automatically in the destination row.

 

[Yongle]

Have you tested what I gave you?

 

[JaccoVis90]

a VLOOKUP function perhaps

 

[2davidc8]

@Yongle
Yes. Did not work, for the following reason:
Using my example that I gave above, I need to first pre-format my destination rows. I used =IF(A1="";"";A1) in cell A301 and copied it across the columns I was interested in. Thus, cell B301 had =IF(B1="";"";B1), cell C301 had =IF(C1="";"";C1), etc. All good so far.
But now if I try to copy the formula to rows 302, 303 and 304, what I get is that the formulas now refer to cells in rows 2, 3, and 4, which is not what I want.
I want row 302 to refer to row 4, and row 304 to refer to row 10.
So, in order to use this solution, I would have to format each destination row one by one. Not very nice when I have to do hundreds.