How can I create a fill that increments based on worksheet numbers

[Guest]

I have a totals worksheet (named "Totals," that tabulates all the information
found on the other 100 worksheets in the database. The other worksheets are
named "0001", "0002", "0003," and so on.

I need to reference data from all 100 worksheets to the Totals sheet. An
example of what I need is:

A2 on the Totals sheet needs to reference Sheet 0001 cell A2
A3 on the Totals sheet needs to reference Sheet 0002 cell A2
A4 on the Totals sheet needs to reference Sheet 0003 cell A2, etc...

I've hunted around with different fill techniques, and different reference
techniques but haven't been able to find anything. Hopefully someone out
there has a better idea than I do. I am open to ALL ideas.

Thanks.

 

[Arvi Laanemets]

Hi

Into A2 enter the formula
=INDIRECT("'" & TEXT(ROW()-1,"0000") & "'!A2")
, and copy down for 100 rows

Arvi Laanemets

 

[Biff]

Hi!

Try this:

I'm assuming the naming convention is like this:

0001
0002
0010
0100

=INDIRECT(TEXT(ROWS($1:1),"0000")&"!A2")

Copy down as needed.

Biff

 

[Guest]

Thanks - it worked great!!!

Hi!

Try this:

I'm assuming the naming convention is like this:

0001
0002
0010
0100

=INDIRECT(TEXT(ROWS($1:1),"0000")&"!A2")

Copy down as needed.

Biff