Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AutoFill Changing Wrong Value
I'm trying to create an analysis sheet that gathers data from 100 worksheets
within the same workbook. I want the same cell reference in each sheet, but to change the sheet number in each cell. The worksheets are named 1 - 100 Here is the formula: ='1'!B3:E3 It changes the cell references only, not the worksheets. I've changed the formula to keep the cell references: ='1'!$B$3:$E$3 I now need the formula to increment the sheet number each time, PLEASE!!! Thanks, Lisa |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AutoFill Changing Wrong Value
Presumably you want to sum data from that range? Try this:
=SUM(INDIRECT("'"&ROW(A1)&"'!B3:E3")) then copy that down as far as you need. Hope this helps. Pete On Mar 9, 9:37*am, Lisa wrote: I'm trying to create an analysis sheet that gathers data from 100 worksheets within the same workbook. I want the same cell reference in each sheet, but to change the sheet number in each cell. The worksheets are named 1 - 100 Here is the formula: ='1'!B3:E3 It changes the cell references only, not the worksheets. I've changed the formula to keep the cell references: ='1'!$B$3:$E$3 I now need the formula to increment the sheet number each time, PLEASE!!! Thanks, Lisa |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AutoFill Changing Wrong Value
Not sure what do you want because your formula ='1'!$B$3:$E$3 gives a #VALUE
error when entered in a single cell. If you want to collect single cell values to single cells, e.g. ='1'!$B$3 in row2 of the summary sheet and you want to change sheet names when the formula is filled down then use this: =INDIRECT("'"&ROW()-1&"'!B3") Otherwise please clarfy your request! -- Regards! Stefi €˛Lisa€¯ ezt Ć*rta: I'm trying to create an analysis sheet that gathers data from 100 worksheets within the same workbook. I want the same cell reference in each sheet, but to change the sheet number in each cell. The worksheets are named 1 - 100 Here is the formula: ='1'!B3:E3 It changes the cell references only, not the worksheets. I've changed the formula to keep the cell references: ='1'!$B$3:$E$3 I now need the formula to increment the sheet number each time, PLEASE!!! Thanks, Lisa |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AutoFill Changing Wrong Value
hi,
Thanks for that. I altered it to read: =INDIRECT("'"&ROW()-13&"'!B3:E3") It works! Thanks and Regards, Lisa "Stefi" wrote: Not sure what do you want because your formula ='1'!$B$3:$E$3 gives a #VALUE error when entered in a single cell. If you want to collect single cell values to single cells, e.g. ='1'!$B$3 in row2 of the summary sheet and you want to change sheet names when the formula is filled down then use this: =INDIRECT("'"&ROW()-1&"'!B3") Otherwise please clarfy your request! -- Regards! Stefi €˛Lisa€¯ ezt Ć*rta: I'm trying to create an analysis sheet that gathers data from 100 worksheets within the same workbook. I want the same cell reference in each sheet, but to change the sheet number in each cell. The worksheets are named 1 - 100 Here is the formula: ='1'!B3:E3 It changes the cell references only, not the worksheets. I've changed the formula to keep the cell references: ='1'!$B$3:$E$3 I now need the formula to increment the sheet number each time, PLEASE!!! Thanks, Lisa |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AutoFill Changing Wrong Value
Thanks, I have combined what both posts advised and ended up with this for
the sum cells: =SUM(INDIRECT("'"&ROW()-13&"'!E98")) Thanks and Regards, Lisa "Pete_UK" wrote: Presumably you want to sum data from that range? Try this: =SUM(INDIRECT("'"&ROW(A1)&"'!B3:E3")) then copy that down as far as you need. Hope this helps. Pete On Mar 9, 9:37 am, Lisa wrote: I'm trying to create an analysis sheet that gathers data from 100 worksheets within the same workbook. I want the same cell reference in each sheet, but to change the sheet number in each cell. The worksheets are named 1 - 100 Here is the formula: ='1'!B3:E3 It changes the cell references only, not the worksheets. I've changed the formula to keep the cell references: ='1'!$B$3:$E$3 I now need the formula to increment the sheet number each time, PLEASE!!! Thanks, Lisa . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AutoFill Changing Wrong Value
You are welcome! Thanks for the feedback, but I still don't understand how
=INDIRECT("'"&ROW()-13&"'!B3:E3") could work. It works as a SUM range like Pete presumed, but not in itself. Clicking the YES button will be appreciated. -- Regards! Stefi €˛Lisa€¯ ezt Ć*rta: hi, Thanks for that. I altered it to read: =INDIRECT("'"&ROW()-13&"'!B3:E3") It works! Thanks and Regards, Lisa "Stefi" wrote: Not sure what do you want because your formula ='1'!$B$3:$E$3 gives a #VALUE error when entered in a single cell. If you want to collect single cell values to single cells, e.g. ='1'!$B$3 in row2 of the summary sheet and you want to change sheet names when the formula is filled down then use this: =INDIRECT("'"&ROW()-1&"'!B3") Otherwise please clarfy your request! -- Regards! Stefi €˛Lisa€¯ ezt Ć*rta: I'm trying to create an analysis sheet that gathers data from 100 worksheets within the same workbook. I want the same cell reference in each sheet, but to change the sheet number in each cell. The worksheets are named 1 - 100 Here is the formula: ='1'!B3:E3 It changes the cell references only, not the worksheets. I've changed the formula to keep the cell references: ='1'!$B$3:$E$3 I now need the formula to increment the sheet number each time, PLEASE!!! Thanks, Lisa |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AutoFill Changing Wrong Value
Thanks for the feedback, Lisa. You seem to have changed the details.
Note that if you use ROW(A1) instead of ROW()-13 in your formula, then this will return 1 whichever row it is in, whereas the second term is dependent on which row you put it in. Pete On Mar 9, 11:09*am, Lisa wrote: Thanks, I have combined what both posts advised and ended up with this for the sum cells: =SUM(INDIRECT("'"&ROW()-13&"'!E98")) Thanks and Regards, Lisa "Pete_UK" wrote: Presumably you want to sum data from that range? Try this: =SUM(INDIRECT("'"&ROW(A1)&"'!B3:E3")) then copy that down as far as you need. Hope this helps. Pete On Mar 9, 9:37 am, Lisa wrote: I'm trying to create an analysis sheet that gathers data from 100 worksheets within the same workbook. I want the same cell reference in each sheet, but to change the sheet number in each cell. The worksheets are named 1 - 100 Here is the formula: ='1'!B3:E3 It changes the cell references only, not the worksheets. I've changed the formula to keep the cell references: ='1'!$B$3:$E$3 I now need the formula to increment the sheet number each time, PLEASE!!! Thanks, Lisa .- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing background colour when changing data in a cell | Excel Discussion (Misc queries) | |||
Insert Calculated Field (wrong Qty*Price = wrong Amount) | Excel Discussion (Misc queries) | |||
VLOOKUP Changing reference cells in autofill | Excel Worksheet Functions | |||
Autofill: Need to autofill one week block, (5) weekday only into cells. | Excel Discussion (Misc queries) | |||
cell height changing when using autofill | Excel Discussion (Misc queries) |