Paste Linking with Absolute Reference
 

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

Paste Linking with Absolute Reference
 

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?
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"MikeDH" wrote in message
...
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

Paste Linking with Absolute Reference
 

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" wrote:

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?
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"MikeDH" wrote in message
...
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

Paste Linking with Absolute Reference
 

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"MikeDH" wrote in message
...
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" wrote:

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?
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"MikeDH" wrote in message
...
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

Paste Linking with Absolute Reference
 

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.

"MikeDH" wrote:

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

Paste Linking with Absolute Reference
 

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

"Sedgwick" wrote:

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.

"MikeDH" wrote:

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

Paste Linking with Absolute Reference
 

Hello - Did you find an alternative solution ?

"MikeDH" wrote:

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

"Sedgwick" wrote:

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.

"MikeDH" wrote:

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