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

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

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

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

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

.

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

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 -