Prev: Drag Down Formula Incorrect
Next: 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 <L...(a)discussions.microsoft.com> 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 <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
>
> > .- Hide quoted text -
>
> - Show quoted text -

First  |  Prev  | 
Pages: 1 2
Prev: Drag Down Formula Incorrect
Next: Count