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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

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
 

Forum statistics

Threads
1,141,011
Messages
5,703,724
Members
421,311
Latest member
tanujath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top