Copy Excel Formula

Microsoft Office professional edition 20...
March 12, 2010 at 08:12:53
Specs: Windows 2000
I have 500+sheet, summary sheet, and total sheet in excel. I need to know how to copy a formula that state (=sheet1'!$B$1) in summary sheet in cell A2 to A3 in the same sheet so that it can read (=sheet2'!$B$1). Is the possible.

See More: Copy Excel Formula

Report •

March 12, 2010 at 08:37:43
I'm missing something here.

You want to copy a formula on your summary sheet
from cell A2 to cell A3
You want the formula to change from =sheet1'$b$1 to =sheet2'$b$1



Report •

March 12, 2010 at 09:11:32
Look up the INDIRECT() and ROW() functions in Excel Help.

Combining them should get you what you are asking for.

Put this formula in A2 and drag it down:


In A2, ROW() - 1 will evaluate to 1 so you'll have:


which evaluates to:


As the ROW() function picks up the number of the Row it is in and subtracts 1, it will increment from 1 to 2 to 3 etc.

P.S. I left out the single quote in my formula...put it back in if you really need it.

Report •

March 12, 2010 at 09:30:33

To create a large number of formulas which cannot be extended by dragging - as in your case where you need the change the sheet number do this:

1. Starting at cell 2 in any empty column (column G for this example) enter 1 in G2, then 2 in G3.
2. Select the two cells and drag them down the 500 or so lines required, so that the last cell has the number of the last worksheet.
3. In the column H also on row 2 enter this:

="XXXSheet" & TEXT(G2,"0") & "!$b$1"

4. Drag this formula down alongside the numbers in column G
5. H2 will now display this

6. Select all the cells from column H and Copy
7. Goto cell A2 and Paste Special - Paste Values
8. Select column A and do a Find and Replace, find XXX and replace with =

You now have a series of formulas referencing cell B1 in all your worksheets.

A bit tedious, but it sure beats the heck out of changing 500 formulas manually.


Report •

Related Solutions

March 12, 2010 at 13:42:02

Thanks for the great advice. So I can do IF THEN statement too. ex: =IF(sheet1'!$B$1=1,sheet1'!$B$2,sheet1'!$B$3).

Thanks alot

Report •

Ask Question