Copy formulae to new sheet problem

  • Thread starter Thread starter Nathan
  • Start date Start date
N

Nathan

Hi does anyone know a solution to copying a formulae to a different sheet
and still have that formulae reference to cells on the original sheet?
I guess this could be manually edited but this is impractical for long
formulas or for multiple formulas.

Thanks

-Nathan
 
Hi Nathan

One way

For one cell you can type a apostrophe before the formula to make it text
'=a1+b1

Or for a range
Select your copy range and use Edit>Replace in the
Menu Bar.

Use replace "=" to " =" (see the space before =)
Without quotes

copy the range to the new sheet and do

Use replace " =" to "="
Without quotes
 
Thanks but this just copies over the formulae the same as if I had made it
absolute with $. The cell references do not refer back to the original
sheet.

For example a simply formulae of sum($a$1:$a$3) on sheet1 copied over to
sheet 2 will add the cells a1, a2 and a3 on sheet2 NOT the cells a1,a2 and
a3 on the original sheet1.

I want to be able to copy a formula from sheet to sheet2 and have it keep
it's reference to the sheet1 cells.

Thanks anyway

-Nathan
 
How about using a helper worksheet.

Say you have your formulas on Sheet1, you want to use them on sheet2. So insert
a new worksheet named sheet3.

Copy the formulas from sheet1 to Sheet3. (same location to keep the formulas
accurate.)

Then back to sheet1 and Cut (not copy the formulas). And paste to sheet2.

Then back to sheet3 and copy|paste back to sheet1.

and delete sheet3 when you're done.
 
And just curious:

Why not just have a formula that refers back to the original formula. If
something changes, you'd only have to fix it once!

With this kind of formula:
=if(sheet1!a1="","",sheet1!a1)
 
Thanks that did the trick, I had only tried to copy not to cut, I actually
wanted the formula moved anyway but was just being careful so tried a copy
first. Strange that copy does not give an option to do a sheet reference as
the cut tool automatically implements.

Thanks very much

-Nathan
 
Back
Top