Paste Linking with Absolute Reference

[Guest]

Just a general question:
Is there any way to either:

1) Paste-special -> Paste Link so the cells are then filled with absolute
references ($x$#) instead of standard reference format (x#)
or
2) Apply absolute reference format to all cell-references in a selected array?

Essentially, I need to be able to paste-link in a way that the information
referenced is the same even if something should be inserted above it in the
referenced sheet. Any ideas or solutions would be greatly appreciated.
-MikeDH

 

[Ragdyer]

I don't understand your problem.

If you create a link where Sheet1, cell D10 is linked to (and displays) the
value in Sheet2, cell E5, and you insert a row in Sheet2, *above* E5, making
that now E6, that value in the new E6 will *still* display on Sheet1, D10.

You should notice that the 'link' formula changes to compensate for the
insertion.

Doesn't this happen for you?

 

[Guest]

Unfortunately, that's what I want to happen, but not what does. I've got
Office 2002 on this machine at work - any chance that was change in the 2003+
releases? On mine, it references the row - in my example, it's full rows, so
23 - and when the contents shift down, it references row 23 and whatever the
new contents are.

 

[Ragdyer]

Post *exactly* the actual formulas that you're using to create the link.

 

[Guest]

Mike,

if I understand your problem correctly, you should be able to work around
this by using a defined name rather than a cell reference.

To create a Defined Name you select the cells, columns or rows that you wish
to read from, Click Insert => Name => Define. Choose a name that you can
associate to the selected cells and click Add ..

This was when you copy/paste your formula it will be looking at the NAME
rather than a cell value. ie. =SUM((50*"Price_per_Beer")/"mates")

If this doesn't explain it clearly, let me know and I'll try again =)

Hope this was of help.

 

[Guest]

Sedgwick,
I had thought about that for a while, but it would unfortunately be too
much work. The sheet in question is a weekly delivery sheet for multiple
farmer-producers, often - especially lately - with 300+ rows to reference.
It just wouldn't be effective for me to name each row every time I enter
something and still hope to get the other tasks done. I suppose I'll just
have to copy-past normally every time I update something.
-MikeDH

 

[Guest]

Hello - Did you find an alternative solution ?

Sedgwick,
I had thought about that for a while, but it would unfortunately be too
much work. The sheet in question is a weekly delivery sheet for multiple
farmer-producers, often - especially lately - with 300+ rows to reference.
It just wouldn't be effective for me to name each row every time I enter
something and still hope to get the other tasks done. I suppose I'll just
have to copy-past normally every time I update something.
-MikeDH