Need Help: 'sheets' function with a variable in a formula
 

I have a function that will pull data from a cell on another sheet:

=RIGHT(Sheet1!$B$1,LEN(Sheet1!$B$1)-FIND(":",Sheet1!$B$1,1))

the problem is that I want to pull the same cell data from hundreds of
sheets (sheets 1 to 200) and list the cell data in a column.

how can I get the "sheet" to change for each consecutive row in the column?

I was thinking I'd have another column with the sheet number (1, 2, 3, etc)
and have a formula within the above forumula to pull that number. Something
like in place of "Sheet1!$B$1" it would be something like "sheets(H2)!$B$1"
where the H2 cell has the value of the sheet... 1, 2, etc. but this is not
working.

I know this must be simple... thanks in advance!



btw... sorry for the cross-post, not sure what forum was best to use.

Why does it have to be simple ?
=INDIRECT("Sheet"&H2&"!$B$1")


"livin" skrev i melding
news:znITe.71916$Ji4.43480@fed1read03...
I have a function that will pull data from a cell on another sheet:

=RIGHT(Sheet1!$B$1,LEN(Sheet1!$B$1)-FIND(":",Sheet1!$B$1,1))

the problem is that I want to pull the same cell data from hundreds of
sheets (sheets 1 to 200) and list the cell data in a column.

how can I get the "sheet" to change for each consecutive row in the
column?

I was thinking I'd have another column with the sheet number (1, 2, 3,
etc)
and have a formula within the above forumula to pull that number.
Something
like in place of "Sheet1!$B$1" it would be something like
"sheets(H2)!$B$1"
where the H2 cell has the value of the sheet... 1, 2, etc. but this is not
working.

I know this must be simple... thanks in advance!



btw... sorry for the cross-post, not sure what forum was best to use.

worked perfectly... THANK YOU SO MUCH!!!


"Harald Staff" wrote in message
...
Why does it have to be simple ?
=INDIRECT("Sheet"&H2&"!$B$1")


"livin" skrev i melding
news:znITe.71916$Ji4.43480@fed1read03...
I have a function that will pull data from a cell on another sheet:

=RIGHT(Sheet1!$B$1,LEN(Sheet1!$B$1)-FIND(":",Sheet1!$B$1,1))

the problem is that I want to pull the same cell data from hundreds of
sheets (sheets 1 to 200) and list the cell data in a column.

how can I get the "sheet" to change for each consecutive row in the
column?

I was thinking I'd have another column with the sheet number (1, 2, 3,
etc)
and have a formula within the above forumula to pull that number.
Something
like in place of "Sheet1!$B$1" it would be something like
"sheets(H2)!$B$1"
where the H2 cell has the value of the sheet... 1, 2, etc. but this is
not
working.

I know this must be simple... thanks in advance!



btw... sorry for the cross-post, not sure what forum was best to use.