Prev: Drag Down Formula IncorrectNext: Count From: Stefi on 9 Mar 2010 07:07 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 From: Pete_UK on 9 Mar 2010 09:02 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 - First  |  Prev  |  Pages: 1 2 Prev: Drag Down Formula IncorrectNext: Count