Copying formulas for multiple tabs

[Peter]

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.

[Ron de Bruin]

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" <[email protected]> wrote in message news:[email protected]...
> 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.

[Peter]

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" <[email protected]> wrote in message news:[email protected]...
> > 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.
>
>
>

[Ron de Bruin]

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> > 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.
>>
>>
>>

[Peter]

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" <[email protected]> wrote in message news:[email protected]...
> > 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" <[email protected]> wrote in message news:[email protected]...
> >> > 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.
> >>
> >>
> >>
>
>
>