Copy multiple sheet forumla

[co]

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

 

[al_b_cnu]

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]

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]

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]

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

 

[co]

Sorry that formula was me just quickly messing about.

List numbers in column A
Formulas in column B

 

[co]

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