AutoFill Changing Wrong Value

[Lisa]

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

 

[Pete_UK]

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

 

[Stefi]

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:

 

[Lisa]

hi,

Thanks for that. I altered it to read:

=INDIRECT("'"&ROW()-13&"'!B3:E3")

It works!
Thanks and Regards, Lisa

 

[Lisa]

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

 

[Stefi]

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:

 

[Pete_UK]

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