Using text in a cell in a formula

[navel151]

I need to have a formula use the text of adjacent cells to determine where to
look for values. I want to grab data from multiple datasheets but I don't
want to have to type the sheet name in every formula, I want the formula to
grab the name of the sheet from an adjacent cell.
i.e. I need a formula like =if(sheet1!B2>0,100,""), but I need a function
that will fill in the sheet name from the text value of an adjacent cell that
would have the sheet name in it as I will be doing this for multiple cells on
the same sheet.

 

[T. Valko]

see your other post

 

[pmartglass]

try something like this
lets say your sheet name is in cell A2

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

it may be hard to see, but there is a ' between the quotation marks before
the cell name and also before the !