Auto increment over multiple worksheets

[Ethan]

I couldn't really figure out how to search the forums for this one...
sorry I just don't know the terms that would make a topic such as this
one show up for me.

My question is, (or is it even possible?) :

I am setting up a summary sheet of multiple worksheets and I'm making
multiple columns. Each column corresponds to a specific cell in each
worksheet I want to be able to drag down a column using the auto
increment feature and have it increment the worksheet so that column 1
row 1 has worksheet 1 cell 1 and column 1 row 2 has worksheet 2 cell 1
etc.

I'd just like an automatic way instead of having to change the
worksheet name each time.

 

[Biff]

Hi!

If the sheet names are the default names: Sheet1, Sheet2, Sheet3, etc OR if
the sheet names are something like 1, 2, 3, etc, OR Week1, Week2, Week3,
etc, then something like this will work:

For SheetX:

=INDIRECT("sheet"&ROW(1:1)&"!A1")

For 1, 2

=INDIRECT(ROW(1:1)&"!A1")

Biff

 

[Gord Dibben]

Ethan

If Sheet1, Sheet2 etc. enter this formula in A1 of your master sheet.

=INDIRECT("'Sheet" & (ROW() & "'!$A$1")

Drag/copy down column B.

If sheets have unique names, enter the sheet names in a column and use this
formula.

=INDIRECT(B1 & "!$A$1")

If spaces in sheetnames use =INDIRECT("'" & B1 & "'!$A$1")

Assume sheet names were in B1:B10 you would copy down 10 cells.

Your ranges may differ, so adjust to suit.


Gord Dibben Excel MVP