If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Copying a cell ref. down a column, changing worksheet # along the
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. |
#2
|
|||
|
|||
Copying a cell ref. down a column, changing worksheet # along the
=INDIRECT("sheet "&ROW()&"!$B$1")
then copy down On 11 Gru, 20:09, olivia wrote: 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. |
#3
|
|||
|
|||
Copying a cell ref. down a column, changing worksheet # along the
You could use this formula instead:
=INDIRECT("'Sheet"&ROW(A1)&"'!$B$1") and then copy it down. Hope this helps. Pete "olivia" wrote in message ... 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. |
#4
|
|||
|
|||
Copying a cell ref. down a column, changing worksheet # along the
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) On Thu, 11 Dec 2008 11:09:01 -0800, olivia wrote: 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. |
#5
|
|||
|
|||
Copying a cell ref. down a column, changing worksheet # along
Thank you all for your help.
"Chip Pearson" wrote: 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) On Thu, 11 Dec 2008 11:09:01 -0800, olivia wrote: 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. |
#6
|
|||
|
|||
Copying a cell ref. down a column, changing worksheet # along
"Chip Pearson" wrote: 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) On Thu, 11 Dec 2008 11:09:01 -0800, olivia wrote: 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. |
Thread Tools | |
Display Modes | |
|
|