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.