Auto increment over multiple worksheets

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.

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


"Ethan" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
>
>
> --
> Ethan
> ------------------------------------------------------------------------
> Ethan's Profile:
> http://www.excelforum.com/member.php...o&userid=24534
> View this thread: http://www.excelforum.com/showthread...hreadid=381267
>

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

On Wed, 22 Jun 2005 10:40:10 -0500, Ethan
<[email protected]> wrote:

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

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


"Ethan" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
>
>
> --
> Ethan
> ------------------------------------------------------------------------
> Ethan's Profile:
> http://www.excelforum.com/member.php...o&userid=24534
> View this thread: http://www.excelforum.com/showthread...hreadid=381267
>

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

On Wed, 22 Jun 2005 10:40:10 -0500, Ethan
<[email protected]> wrote:

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

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


"Ethan" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
>
>
> --
> Ethan
> ------------------------------------------------------------------------
> Ethan's Profile:
> http://www.excelforum.com/member.php...o&userid=24534
> View this thread: http://www.excelforum.com/showthread...hreadid=381267
>

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

On Wed, 22 Jun 2005 10:40:10 -0500, Ethan
<[email protected]> wrote:

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

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

On Wed, 22 Jun 2005 10:40:10 -0500, Ethan
<[email protected]> wrote:

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

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


"Ethan" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
>
>
> --
> Ethan
> ------------------------------------------------------------------------
> Ethan's Profile:
> http://www.excelforum.com/member.php...o&userid=24534
> View this thread: http://www.excelforum.com/showthread...hreadid=381267
>

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


"Ethan" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
>
>
> --
> Ethan
> ------------------------------------------------------------------------
> Ethan's Profile:
> http://www.excelforum.com/member.php...o&userid=24534
> View this thread: http://www.excelforum.com/showthread...hreadid=381267
>

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

On Wed, 22 Jun 2005 10:40:10 -0500, Ethan
<[email protected]> wrote:

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

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


"Ethan" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
>
>
> --
> Ethan
> ------------------------------------------------------------------------
> Ethan's Profile:
> http://www.excelforum.com/member.php...o&userid=24534
> View this thread: http://www.excelforum.com/showthread...hreadid=381267
>

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

On Wed, 22 Jun 2005 10:40:10 -0500, Ethan
<[email protected]> wrote:

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

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


"Ethan" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
>
>
> --
> Ethan
> ------------------------------------------------------------------------
> Ethan's Profile:
> http://www.excelforum.com/member.php...o&userid=24534
> View this thread: http://www.excelforum.com/showthread...hreadid=381267
>

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

On Wed, 22 Jun 2005 10:40:10 -0500, Ethan
<[email protected]> wrote:

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

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

On Wed, 22 Jun 2005 10:40:10 -0500, Ethan
<[email protected]> wrote:

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

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


"Ethan" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
>
>
> --
> Ethan
> ------------------------------------------------------------------------
> Ethan's Profile:
> http://www.excelforum.com/member.php...o&userid=24534
> View this thread: http://www.excelforum.com/showthread...hreadid=381267
>

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


"Ethan" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
>
>
> --
> Ethan
> ------------------------------------------------------------------------
> Ethan's Profile:
> http://www.excelforum.com/member.php...o&userid=24534
> View this thread: http://www.excelforum.com/showthread...hreadid=381267
>

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

On Wed, 22 Jun 2005 10:40:10 -0500, Ethan
<[email protected]> wrote:

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

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


"Ethan" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
>
>
> --
> Ethan
> ------------------------------------------------------------------------
> Ethan's Profile:
> http://www.excelforum.com/member.php...o&userid=24534
> View this thread: http://www.excelforum.com/showthread...hreadid=381267
>

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

On Wed, 22 Jun 2005 10:40:10 -0500, Ethan
<[email protected]> wrote:

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

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


"Ethan" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
>
>
> --
> Ethan
> ------------------------------------------------------------------------
> Ethan's Profile:
> http://www.excelforum.com/member.php...o&userid=24534
> View this thread: http://www.excelforum.com/showthread...hreadid=381267
>

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

On Wed, 22 Jun 2005 10:40:10 -0500, Ethan
<[email protected]> wrote:

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

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


"Ethan" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
>
>
> --
> Ethan
> ------------------------------------------------------------------------
> Ethan's Profile:
> http://www.excelforum.com/member.php...o&userid=24534
> View this thread: http://www.excelforum.com/showthread...hreadid=381267
>

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

On Wed, 22 Jun 2005 10:40:10 -0500, Ethan
<[email protected]> wrote:

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