Hello All

I have a workbook that contains about 240 worksheets and a summary worksheet
and I need to copy over totals from each of the individual worksheets into
the summary sheet. For example I need to use cell B1 for each of the sheets
and enter it into column 2 of the summary sheet (this would go down 240 rows,
one for each worksheet). Instead of having to write a formula for each of the
240 rows, is there a way I could enter the formula for the first few rows and
copy (or drag) the formula down for the rest of the rows where the tab number
will change automatically?

Thanks.

Hi Peter

If you sheet names are sheet1,sheet2..... sheet30
Then copy this formula in A1 and copy down

=INDIRECT("'Sheet" & ROW()&"'!B1")


You can also do it with a macro
http://www.rondebruin.nl/summary.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Peter" wrote in message ...
Hello All

I have a workbook that contains about 240 worksheets and a summary worksheet
and I need to copy over totals from each of the individual worksheets into
the summary sheet. For example I need to use cell B1 for each of the sheets
and enter it into column 2 of the summary sheet (this would go down 240 rows,
one for each worksheet). Instead of having to write a formula for each of the
240 rows, is there a way I could enter the formula for the first few rows and
copy (or drag) the formula down for the rest of the rows where the tab number
will change automatically?

Thanks.

Thanks for the help, however I think my explanation of what I needed was
confusing, let me clear it up.

Example: If you enter 1 in a row, then enter 2 below it, and finally enter 3
below that you can drag the string of numbers down and it will automatically
increase to 4,5,6, etc.

My tabs are named as numbers 1,2,3, etc. I need to take cell B1 from each
tab into the summary sheet. Here is the original formula that is not working:
='1'!$B$1

B1 is always the cell which is right, but I need the tab to increase
automatically when I drag it down.

Thanks.

"Ron de Bruin" wrote:

Hi Peter

If you sheet names are sheet1,sheet2..... sheet30
Then copy this formula in A1 and copy down

=INDIRECT("'Sheet" & ROW()&"'!B1")


You can also do it with a macro
http://www.rondebruin.nl/summary.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Peter" wrote in message ...
Hello All

I have a workbook that contains about 240 worksheets and a summary worksheet
and I need to copy over totals from each of the individual worksheets into
the summary sheet. For example I need to use cell B1 for each of the sheets
and enter it into column 2 of the summary sheet (this would go down 240 rows,
one for each worksheet). Instead of having to write a formula for each of the
240 rows, is there a way I could enter the formula for the first few rows and
copy (or drag) the formula down for the rest of the rows where the tab number
will change automatically?

Thanks.

Remove Sheet

Copy this in the first row of your sheet and copy down

=INDIRECT( "'" & ROW()&"'!B1")

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Peter" wrote in message ...
Thanks for the help, however I think my explanation of what I needed was
confusing, let me clear it up.

Example: If you enter 1 in a row, then enter 2 below it, and finally enter 3
below that you can drag the string of numbers down and it will automatically
increase to 4,5,6, etc.

My tabs are named as numbers 1,2,3, etc. I need to take cell B1 from each
tab into the summary sheet. Here is the original formula that is not working:
='1'!$B$1

B1 is always the cell which is right, but I need the tab to increase
automatically when I drag it down.

Thanks.

"Ron de Bruin" wrote:

Hi Peter

If you sheet names are sheet1,sheet2..... sheet30
Then copy this formula in A1 and copy down

=INDIRECT("'Sheet" & ROW()&"'!B1")


You can also do it with a macro
http://www.rondebruin.nl/summary.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Peter" wrote in message ...
Hello All

I have a workbook that contains about 240 worksheets and a summary worksheet
and I need to copy over totals from each of the individual worksheets into
the summary sheet. For example I need to use cell B1 for each of the sheets
and enter it into column 2 of the summary sheet (this would go down 240 rows,
one for each worksheet). Instead of having to write a formula for each of the
240 rows, is there a way I could enter the formula for the first few rows and
copy (or drag) the formula down for the rest of the rows where the tab number
will change automatically?

Thanks.

Perfect, thank you Ron.

"Ron de Bruin" wrote:

Remove Sheet

Copy this in the first row of your sheet and copy down

=INDIRECT( "'" & ROW()&"'!B1")

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Peter" wrote in message ...
Thanks for the help, however I think my explanation of what I needed was
confusing, let me clear it up.

Example: If you enter 1 in a row, then enter 2 below it, and finally enter 3
below that you can drag the string of numbers down and it will automatically
increase to 4,5,6, etc.

My tabs are named as numbers 1,2,3, etc. I need to take cell B1 from each
tab into the summary sheet. Here is the original formula that is not working:
='1'!$B$1

B1 is always the cell which is right, but I need the tab to increase
automatically when I drag it down.

Thanks.

"Ron de Bruin" wrote:

Hi Peter

If you sheet names are sheet1,sheet2..... sheet30
Then copy this formula in A1 and copy down

=INDIRECT("'Sheet" & ROW()&"'!B1")


You can also do it with a macro
http://www.rondebruin.nl/summary.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Peter" wrote in message ...
Hello All

I have a workbook that contains about 240 worksheets and a summary worksheet
and I need to copy over totals from each of the individual worksheets into
the summary sheet. For example I need to use cell B1 for each of the sheets
and enter it into column 2 of the summary sheet (this would go down 240 rows,
one for each worksheet). Instead of having to write a formula for each of the
240 rows, is there a way I could enter the formula for the first few rows and
copy (or drag) the formula down for the rest of the rows where the tab number
will change automatically?

Thanks.