Move forumlas to another sheet but keep references to first sheet

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?
 
B

BMC

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?
 
B

BMC

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!
 
M

mdavison

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.
 

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

Similar Threads

Mixed references 2
Copy a formula using Vlookup 2
hlookup with the sheet name is a reference from another cell 2
Linking to Access data without destroying formulas 16
Remove workbook reference but keep worksheet references 2
About references 1
Code works on sheet but not from code-to-sheet 2
Issue with local named relative range 2

Top