Filling Sheet numbers in a column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mikresources - 24 Sep 2007 01:12 GMT

There are several sheets in the workbook titled Sh00, Sh10,Sh20...... I am
compiling data form the sheets on to a sheet tilted Sh700. In one of the
columns the formula in the first cell is: =Sh00!$L$10. The data in the
subsequent cells will increment the previous sheet by 10, i.e. =Sh10!$L$10,
=Sh20!$L$10... How can fill the remaining cells without having to manually
chaning each Sheet numbers?
Thank you

Reply to this Message

Gary''s Student - 24 Sep 2007 01:34 GMT

Let's start with Sh30 ( you already have Sh00, Sh10, and Sh20). In the third
row of any un-used column (say Z) enter:

="Sh"&10*(ROW()-1)&"!L10" and copy down. You will see:

Sh30!L10
Sh40!L10
Sh50!L10
Sh60!L10
Sh70!L10 and so on (WE HAVE INCREMENTED THE SHEET NUMBER not the cell)

If we put this stuff in Z3 on down, then elsewhere enter:

=indirect(Z3,TRUE) and copy down

Signature

Gary''s Student - gsnu200746

> There are several sheets in the workbook titled Sh00, Sh10,Sh20...... I am
> compiling data form the sheets on to a sheet tilted Sh700. In one of the
[quoted text clipped - 3 lines]
> chaning each Sheet numbers?
> Thank you

Reply to this Message

mikresources - 24 Sep 2007 02:44 GMT

Thank You for the information, The only remaining item is that Sh**!L10
appears in the cell (I used column z) as data instead of the data in cell
L10 of each specific sheet. I am not sure what to do with =indirect(Z3,TRUE)

> Let's start with Sh30 ( you already have Sh00, Sh10, and Sh20). In the third
> row of any un-used column (say Z) enter:
[quoted text clipped - 18 lines]
> > chaning each Sheet numbers?
> > Thank you

Reply to this Message

Gary''s Student - 24 Sep 2007 04:10 GMT

Use Valko's solution...it does not need any extra helper cells.

Signature

Gary''s Student - gsnu200746

> Thank You for the information, The only remaining item is that Sh**!L10
> appears in the cell (I used column z) as data instead of the data in cell
[quoted text clipped - 22 lines]
> > > chaning each Sheet numbers?
> > > Thank you

Reply to this Message

T. Valko - 24 Sep 2007 03:43 GMT

Try this:

=INDIRECT("Sh"&TEXT(ROWS(A$1:A1)*10-10,"00")&"!L10")

Copy down as needed.

This will start with Sh00.

Signature

Biff
Microsoft Excel MVP

> There are several sheets in the workbook titled Sh00, Sh10,Sh20...... I am
> compiling data form the sheets on to a sheet tilted Sh700. In one of the
[quoted text clipped - 4 lines]
> chaning each Sheet numbers?
> Thank you

Reply to this Message