Prev: Drag Down Formula Incorrect
Next: Count
From: Lisa on 9 Mar 2010 04:37
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 04:58
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 <L...(a)discussions.microsoft.com> 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

From: Stefi on 9 Mar 2010 05:29
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: Lisa on 9 Mar 2010 05:41
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: Lisa on 9 Mar 2010 06:09
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 <L...(a)discussions.microsoft.com> 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
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Drag Down Formula Incorrect
Next: Count