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

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
--
Gary''s Student - gsnu200746


"mikresources" wrote:

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

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)
"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200746


"mikresources" wrote:

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

Try this:

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

Copy down as needed.

This will start with Sh00.



--
Biff
Microsoft Excel MVP


"mikresources" wrote in message
...
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

Use Valko's solution...it does not need any extra helper cells.
--
Gary''s Student - gsnu200746


"mikresources" wrote:

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)
"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200746


"mikresources" wrote:

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