Copy formula over a range

gaj104

Well-known Member
Joined
Nov 9, 2002
Messages
864
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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Well-known Member
Joined
Nov 9, 2002
Messages
864
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
RANGE FORMULA (1st cell)
A2:C2 =SUM(INDIRECT(A$1&"!A1:A10"))

[Table-It] version 06 by Erik Van Geit
 

Watch MrExcel Video

Forum statistics

Threads
1,113,811
Messages
5,544,444
Members
410,610
Latest member
renatha prado
Top