# Copy multiple sheet forumla

#### co

##### New Member
I'm trying to get a summary sheet to work on a workbook with 100 sheets. The formulas need to

look like the following:

=Sheet2!\$A\$1
=Sheet3!\$A\$1
=Sheet4!\$A\$1 ...and so on

but when I try to copy the first formula down it doesn't add the following sheet number. It

ends up as:

=Sheet2!\$A\$1
=Sheet2!\$A\$1
=Sheet2!\$A\$1 ...the same formula repeated

Any ideas on how to copy the formula down? (using Excel 97)

Cheers

co

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### al_b_cnu

##### Well-known Member
Hi,

One way would be to ...

Place the text 'Sheet1" in , e.g.B1 & drag it down the column, then in C1 place the formula =INDIRECT(B1&"!A1") & drag it down the column.

HTH

Alan

#### Yesuslave

##### Active Member
Along those same lines, you can do a quick series down column A which lists 1 through 100 or so.

Then in COlumn B put in: =INDIRECT("Sheet" & A1 & "!A1") from B1 down

That should do it for you.

-Josh

#### co

##### New Member
Thanks for the quick replies, but when I enter the formulas you've given, it just stays there i.e. =INDIRECT("Sheet"&B9&"!A1") like text.

What am I doing wrong??

Cheers

co

#### Yesuslave

##### Active Member
What column are you putting the list of numbers in, and what column are you putting your formula in?

#### co

##### New Member
Sorry that formula was me just quickly messing about.

List numbers in column A
Formulas in column B

#### co

##### New Member
Sorry guys. It works as you said. The reason it was showing the formula was because I had SHOW formulas in the options menu!

What a......... I'll leave the blank for you to complete..

Many thanks

co

Replies
1
Views
74
Replies
3
Views
125
Replies
3
Views
87
Replies
3
Views
218
Replies
1
Views
220

1,186,364
Messages
5,957,427
Members
438,305
Latest member
farmerje

### 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.

### Which adblocker are you using?

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

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