Excel - copy absolute cell references (within the range) as relati

M

Merf1013

I would like to suggest that, when copying a range with formulas containing
absolute cell references, Excel should copy the reference as absolute if the
referenced cell is OUTSIDE the copied range, but as a "relative" absolute
reference if the referenced cell is WITHIN the copied range.

For example, suppose a table (in range A5:G15) includes formulas which refer
to the address $D$10 (which is part of the table) and also to cell $B$2
(which is outside the table). To create a duplicate table, one would copy
the range A5:G15 to another location (for example, K5:Q15). As Excel
currently funtions, the formulas in the new table would still refer back to
cells $D$10 and $B$2. One has to manually change the formulas to refer to
cell $N$10 (in the new table) instead of $D$10 (in the old table).

I would suggest a feature (possibly user-selectable within Options?) that,
in above example, would copy the $B$2 reference normally, but would copy the
$D$10 references as $N$10. That is, absolute references to cells WITHIN the
copied range would copy as absolute references to the same RELATIVE cell in
the new pasted range. This would allow the copying of large blocks or tables
such that all references within that block function similarly to those in the
original block.


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...f-8d3c3dd1b9f5&dg=microsoft.public.excel.misc
 
N

Nick Hodge

In this example you could keep the $B$2 static, by keeping it's row and
column absolute and then with the $D$10 reference, make it D$10, this would
then make the column relative and the row absolute, in your example this
would make it N$10 when copied to the right.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

(e-mail address removed)
www.nickhodge.co.uk
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top