Microsoft Office professional edition 20...

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.

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$1Correct?

MIKE

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:

=INDIRECT("Sheet"&ROW()-1&"!$B$1")

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

=INDIRECT("Sheet1"&"!$B$1")

which evaluates to:

=Sheet1!$B$1

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.

Hi, 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 thisXXXSheet1!$b$1

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.

Regards

Humar, 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

Ask Your Question

Weekly Poll