How to move a formula from one sheet to another

[scott.siegler]

Hello,

I was wondering if anyone knows a way to move a formula from one sheet
to another such that when the formula is moved to the new sheet, it
still points to the first sheet.

For example, on sheet 1, i have a formula in cell B1 = A1 + A2

I want to be able to copy/move the formula in cell B1 to sheet 2 such
that it would still reference cells A1 and A2 on sheet 1. Is there
anyway way/trick to do this other than adding 'sheet 1' and absolute
references to every formula i want to move?

any help would be greatly appreciated.

scott

 

[MartinShort]

The cheat's way would be to use [Ctrl]+"¬" to display the actual
formulae.
Copy the formulae to the new sheet and then use Find & Replace to edit
the reference prefixing each one with the sheet reference.

eg =A1 would become =Sheet1!A1

To switch Excel back to normal mode, use [Ctrl]+"¬" again.