Copy multiple sheet forumla

co

New Member
Joined
Jan 30, 2004
Messages
42
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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,518
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
Joined
Sep 19, 2003
Messages
297
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
Joined
Jan 30, 2004
Messages
42
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
Joined
Sep 19, 2003
Messages
297

ADVERTISEMENT

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

co

New Member
Joined
Jan 30, 2004
Messages
42
Sorry that formula was me just quickly messing about.

List numbers in column A
Formulas in column B
 

co

New Member
Joined
Jan 30, 2004
Messages
42
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,882
Messages
5,766,896
Members
425,383
Latest member
IllDo

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