Copying a cell ref. down a column, changing worksheet # along the

[olivia]

I am referencing the same cell from multiple worksheets onto a "summary"
worksheet. for example,
A1='sheet 1'!$B$1
then I want to copy and paste down the column
A2='sheet 2'!$B$1
A3='sheet 3'!$B$1
Ax ='sheet x'!$B$1

Is there a way to have the sheet number change as I drag down? When I do,
the same sheet and cell number are copied.
Thanks.

 

[Jarek Kujawa]

=INDIRECT("sheet "&ROW()&"!$B$1")

then copy down

 

[Pete_UK]

You could use this formula instead:

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

and then copy it down.

Hope this helps.

Pete

 

[Chip Pearson]

There isn't any built-in support for what you need, but you can use
the INDIRECT function to create the sheet and cell references.
Assuming that your sheets are named Sheet1, Sheet2, Sheet3, etc, you
can enter the formula

=INDIRECT("Sheet"&ROW()+1-ROW($C$8)&"!$B$3")

Change the reference to $C$8 to the first cell that is to use the
INDIRECT reference. Then, copy this formula down for as many cells as
you need. If you copy this to more rows than there are worksheets,
you'll get a #REF error.

If your worksheets have names other than "SheetN", you can create a
list of all worksheet in some range of cells, say G4:G6, and then use
the formula

=INDIRECT(OFFSET($G$4,ROW()-ROW($E$8),0)&"!$B$3")

Change the reference to $G$4 to the first cell of the range that
contains the worksheet names, and change the reference to $E$8 to the
first cell in which you enter this formula. Then, copy down into as
many rows as there are worksheet names in the list. If you copy down
farther, you'll get #REF errors.


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[olivia]

Thank you all for your help.

There isn't any built-in support for what you need, but you can use
the INDIRECT function to create the sheet and cell references.
Assuming that your sheets are named Sheet1, Sheet2, Sheet3, etc, you
can enter the formula

=INDIRECT("Sheet"&ROW()+1-ROW($C$8)&"!$B$3")

Change the reference to $C$8 to the first cell that is to use the
INDIRECT reference. Then, copy this formula down for as many cells as
you need. If you copy this to more rows than there are worksheets,
you'll get a #REF error.

If your worksheets have names other than "SheetN", you can create a
list of all worksheet in some range of cells, say G4:G6, and then use
the formula

=INDIRECT(OFFSET($G$4,ROW()-ROW($E$8),0)&"!$B$3")

Change the reference to $G$4 to the first cell of the range that
contains the worksheet names, and change the reference to $E$8 to the
first cell in which you enter this formula. Then, copy down into as
many rows as there are worksheet names in the list. If you copy down
farther, you'll get #REF errors.


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[2traksDavid]

There isn't any built-in support for what you need, but you can use
the INDIRECT function to create the sheet and cell references.
Assuming that your sheets are named Sheet1, Sheet2, Sheet3, etc, you
can enter the formula

=INDIRECT("Sheet"&ROW()+1-ROW($C$8)&"!$B$3")

Change the reference to $C$8 to the first cell that is to use the
INDIRECT reference. Then, copy this formula down for as many cells as
you need. If you copy this to more rows than there are worksheets,
you'll get a #REF error.

If your worksheets have names other than "SheetN", you can create a
list of all worksheet in some range of cells, say G4:G6, and then use
the formula

=INDIRECT(OFFSET($G$4,ROW()-ROW($E$8),0)&"!$B$3")

Change the reference to $G$4 to the first cell of the range that
contains the worksheet names, and change the reference to $E$8 to the
first cell in which you enter this formula. Then, copy down into as
many rows as there are worksheet names in the list. If you copy down
farther, you'll get #REF errors.


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)