Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel - copy absolute cell references (within the range) as relati
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/comm...lic.excel.misc |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel - copy absolute cell references (within the range) as relati
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 HIS www.nickhodge.co.uk "Merf1013" wrote in message ... 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/comm...lic.excel.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel insits that I use absolute cell references | Excel Discussion (Misc queries) | |||
copy excel cell to cell in word table using excel macro | Excel Discussion (Misc queries) | |||
Automatically Changing Cell Reference's when Pasting in Excel | Excel Discussion (Misc queries) | |||
Unable to Copy COUNTA() with cell references | Excel Worksheet Functions | |||
Excel: how to formulate conditional cell references | Excel Worksheet Functions |