Using text in a cell in a formula
 

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!B20,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.

Using text in a cell in a formula
 

see your other post

--
Biff
Microsoft Excel MVP


"navel151" wrote in message
...
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!B20,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.

Using text in a cell in a formula
 

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 !


"navel151" wrote:

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!B20,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.