Assuming that you have the sheet names in A2, A3, A4 etc, put this
formula in B2:

=INDIRECT("'"&A2&"'!B2")

then copy down. The apostrophes enable you to have sheet names with
spaces in.

Hope this helps.

Pete

On Feb 20, 7:24*am, navel151
wrote:
I need a way to get text from a cell put into a function such that the text
fills in the name of the worksheet to look for the data in.

i.e. I want to look for cell B2 in worksheet test, then the next cell down I
want to look for cell B2 in worksheet answer, etc but I don't want to have to
modify the formula to input the worksheet name every time. *I want a way for
the worksheet name to be grabbed from an adjacent cell and populate the
formula that already has the cell B2 in it.

Thx.