Using text in a cell in a formula

N

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.
 
P

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 !
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

fill in date into adjacent cell automatically 2
Please help with a date formula. 3
Complex formula help , please. 13
Reference text in a cell inside a formula to specify a sheet name 4
Excel If a Cell Contains a Certain Text (From Another Table) 0
filling down a formula using =TEXT() 4
Using =IF(B10<>"",COUNTA($B$10:B10),"") to number rows in columns other than A 2
Dependent "If" formulas 2

Top