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]

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

Ok - I understand your problem and it is easily resolved

Ignore the "zero" problem for the moment and take time to understand the indirect function
( after understanding INDIRECT replace the cell reference in the formulas I gave you above with the INDIRECT equivalents)

I want you to identify a column in rows 301 onward where you can place the ROW number that the formula is going to need
I used column Q, so in column Q I have
Q301 1, Q302 4 etc Q303 10

And now you can use a single formula copied down and across (amend Q to the column you used)
In cell A301
=INDIRECT(ADDRESS($Q301,COLUMN(A301)))
Rows 302 and 303 will refer to rows 4 and 10 respectively

row reference comes from column Q
column reference comes from the column that COLUMN(A301) becomes as you drag it across

That should resolve your problem

 

[2davidc8]

Thanks, @Yongle. I need to play a bit with the INDIRECT and ADDRESS functions and see if I can this work. Will let you know!

 

[2davidc8]

@Yongle
OK, it takes a bit of work to set up column Q, and your solution does solve the problem of making a second table that is basically a subset of the main table, where the rows copied from the main table are not continuous.
Unfortunately, however, blank cells from the source rows still come over with a zero in the destination cells.
Back in the day, I remember there was programming language where we could just say BLANKWHENZERO and convert any zeroes to blanks. It would make a spreadsheet so much cleaner without having all those zeroes all over the place in the destination area. I'm surprised that Excel does not provide for this in their PasteLink or similar feature. I've looked around for other functions, but don't seem to see anything that might help. I hope I don't have to go the VBA route.

 

[Yongle]

it takes a bit of work to set up column Q

Is the not the same amount of effort as PasteLinking each row?
(one number per row or one PasteLink per row)

I've looked around for other functions, but don't seem to see anything that might help. I hope I don't have to go the VBA route.

Keep developing the formula....

When source rows are not moving relative to destination rows replace reference with INDIRECT reference

The earlier formula for numbers
=IF(ISNUMBER(A1),A1,"")
becomes
=IF(ISNUMBER(INDIRECT(ADDRESS($Q301,COLUMN(A301)))),INDIRECT(ADDRESS($Q301,COLUMN(A301))),"")

The earlier formula for text
=T(A1)
becomes
=T(INDIRECT(ADDRESS($Q301,COLUMN(A301))))


To deal with both numbers and text in the same formula
The original formulas were provided in post#3 before I appreciated your preference to be able to copy a single formula down and across

1. start with
=IF(ISNUMBER(A1),A1,"")

2. replace ELSE with what you want to happen if it is text
=IF(ISNUMBER(A1),A1,T(A1))

3. next replace cell reference with INDIRECT reference
=IF(ISNUMBER(INDIRECT(ADDRESS($Q301,COLUMN(A301)))),INDIRECT(ADDRESS($Q301,COLUMN(A301))),T(INDIRECT(ADDRESS($Q301,COLUMN(A301)))))

 

[Yongle]

An alternative workaround for you to consider

The above is only necessary because Paste Link does not distinguish between blanks, null strings and genuine zeros. It may be frustrating, but is an absolute constraint.

Your stipulation that destination values MUST distinguish between genuine zeros and blanks is in direct conflict with that constraint.

Is there another way?
Yes - give Paste Link what it needs to give you what you want
Replacing "blanks" with "spaces" in your SOURCE cells looks the same, but is handled differently by Paste Link

Everyone uses this method to display only the "required" result ...
=IF(Z100="X",200,"")

... which of course creates a blank cell (causing you a problem with Paste Link)

But replacing the conventional null string ...
=IF(Z100="X",200,"")

... with a space ...
=IF(Z100="X",200," ")

... would eliminate your issue

You have already stated that zeros are always zeros in the source - so we do not need to concern ourselves with those cells

Caveat
(Without an additional step...) this only gives desired result where a blank cell is the result of a formula
Are any of the underlying cells truly empty or contain null strings as a result of copy/paste etc?

 

[2davidc8]

@Yongle
Thank you for sticking with me this long. Somehow my reply to you yesterday didn't post, so I'll send you a quick summary.
The problem with the column Q solution is that if I add or delete rows to the main table, that column Q list will have to be redone. I'm trying to avoid extra maintenance work by using PasteLink.
Your idea of going through the main table and replacing all blanks (or null strings) with a space is excellent and it works. It would be a one-time deal for me go through the entire table and put spaces in null cells. Then the whole thing works.
I originally had thought of just going through with the PasteLink, winding up with zeroes in the destination sub-table where there were nulls in the main table, then running a macro to turn all zeroes into spaces. But there is a limitation to this solution; this solution is no good if zero is a valid entry. In my case, this would be OK, because a zero is not a valid entry in my table.

 

[Yongle]

The problem with the column Q solution is that if I add or delete rows to the main table, that column Q list will have to be redone. I'm trying to avoid extra maintenance work by using PasteLink.

Easily resolved by using named ranges like this....
Select cell A4 and name it "SALES"
In cell Q302 enter formula = ROW(SALES) which returns 4
Now delete row 3 and cell Q302 value changes to 3 which is the correct value for the formula
I am glad that you have found something that will work for you. Thanks for the feedback

 

[Yongle]

or even simpler without using named ranges...

In cell Q302 enter formula =ROW(A4) which returns 4
Now delete row 3 and Q302 returns 3

 

[2davidc8]

Ah, I see! Now we're really getting fancy! I'll remember that trick.

Thank you, sir!

 

[John Watkins]

An alternative workaround for you to consider

The above is only necessary because Paste Link does not distinguish between blanks, null strings and genuine zeros. It may be frustrating, but is an absolute constraint.

Your stipulation that destination values MUST distinguish between genuine zeros and blanks is in direct conflict with that constraint.

Is there another way?
Yes - give Paste Link what it needs to give you what you want
Replacing "blanks" with "spaces" in your SOURCE cells looks the same, but is handled differently by Paste Link

Everyone uses this method to display only the "required" result ...
=IF(Z100="X",200,"")

... which of course creates a blank cell (causing you a problem with Paste Link)

But replacing the conventional null string ...
=IF(Z100="X",200,"")

... with a space ...
=IF(Z100="X",200," ")

... would eliminate your issue

You have already stated that zeros are always zeros in the source - so we do not need to concern ourselves with those cells

Caveat
(Without an additional step...) this only gives desired result where a blank cell is the result of a formula
Are any of the underlying cells truly empty or contain null strings as a result of copy/paste etc?

Yongle, i have a similar issue but I do have underlying cells that are truly empty, how can I use Paste Links in this instance?