Move forumlas to another sheet but keep references to first sheet

  • Thread starter Thread starter BMC
  • Start date Start date
B

BMC

I need to copy a section of one sheet to another.

The section contains formulas referring to the same sheet.

When pasted into the second, the formulas should refer to the same cells
on the first sheet.

If I copy a formula with relative references e.g. =sum(A1:A5), it is
pasted as =sum(#ref!).

If I paste a formula with an absolute reference, e.g. it is pasted as
the same absolute reference e.g. =sum($A$1:$A$5) which refers to the
current sheet, not the originating sheet.

What I need it to be is =SUM(Sheet1!A1:A5)

How do I do this?
 
Either seems to result in the same, but anyway, any ideas how to move a
load of formulas to a second sheet and refer to the original shhet, not
the respective cells in the new sheet?
 
Oh I've been a silly old hector!

Cut and paste rather than copy and paste does it, which stands to
reasons as the point of the exercise was that I wanted to move the formulas!
 
Why do they need to be "moved" ?? maybe youare creating stress for yourself?
maybe you can copy the current sheet - rename the tab and modify in or out
whatever you need to from the second copy of the sheet?
Seems a whole lot easier than copy/paste/rename/find and replace....

Just a thought.
 
Back
Top