Copy formula over a range

[gaj104]

Hi

How would I go about duplicating formulas in a range. Usually I would F2 it and manually highlight and copy.

One method I could do it to put $ to fix the cells, but wondering if there was a more efficient way to do this?

Hope it makes sense

Thanks

 

[Richard Schollar]

Hi

I would tend to replace the '=' with either ''=' or '@' and then copy and paste that. Then do a Find/Replace on your range and replace the @/' with an = or whatever's appropriate to reactivate the formulas.

Best regards

Richard

This of course assumes you don't want references to alter.

 

[erik.van.geit]

Hi,

duplicating formulas

the same formula in different cells ? (with same result)
why would you do that ?
perhaps the answer to this question could lead to another solution
(you were talking about efficiency)

kind regards,
Erik

 

[gaj104]

Hi Richard,

I like the idea with the '=

Was thinking is this possible to put into some sort of macro / udf?

Hi Erik

One example what Im doing is coding up budgets. The 2006 figures in column A are referenced already i.e. =sum(PL2006!A1:A10)

In column B i want =sum(PL2005!A1:A10)

So I just do the F2 copy method and then a find and replace 2006 with 2005 in column B.

This is just one example, and I use the F2 copy formula method on a variety of sheets and workbooks. So was looking for a generic way of doing this.

 

[erik.van.geit]

just an idea
in PL2006 I've put "10" in all cells
next sheet has "20" in all cells and next one "30", hence the results 100, 200, 300
  A      B      C      
1 PL2006 PL2005 PL2004 
2 100    200    300    

test
[Table-It] version 06 by Erik Van Geit

RANGE FORMULA (1st cell)
A2:C2 =SUM(INDIRECT(A$1&"!A1:A10"))

[Table-It] version 06 by Erik Van Geit