MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Want to use the value of a cell in a formula


Posted by David Mehl on February 13, 2002 1:28 PM

Have a 2 sheet workbook, Sheet1 and Sheet2

On Sheet1 I have cell A1 with text entry of "B10"

Want to create a formula in Sheet1 cell D1 that gets the value from =Sheet2!B10, but want to do it by referencing the text in Sheet1!A1.

Have tried various combinations like =Sheet2!(Sheet1A1) as well as the CELL and CONCATENATE functions. Nothing seems to work.

Impossible?


Posted by Aladin Akyurek on February 13, 2002 1:33 PM

See: 21178a.html

Posted by David Mehl on February 13, 2002 3:47 PM

Perfect Solution - THANKS!

SOLUTION WAS:

=INDIRECT(A2&"!b2")
Substitutes the contents of A2 for A2 in the formula.

So,
A2=sheet2, formula is sheet2!b2
A2=sheet3, formula is sheet3!b2

GREAT!