Help with formula

[arioch10]

Hi,

Is it possible to use the text in a cell as part of a formula or function in another cell. Say I'm in a sheet called TEST and cells A1, B1, and C1 look like this:

BLUE
GREEN
RED

and I have several different worksheets, 3 of which are named BLUE, GREEN, and RED. Normally if I wanted to link to the data in cell F5 in one of those sheets, the formula would look like:
=+'BLUE'!F5

My question is, is there someway I can use the data in cell A1 of my current sheet to tell the formula to look at sheet BLUE instead of actually typing BLUE or having to click over to that sheet while writing the formula. I know the following formula is invalid but this may help understand what I am trying to do.
=+'(A1)'!F5 or something along those lines instead of =+'BLUE'!F5

Thanks for your help!
Jeff

[VBA Noob]

Hi,

Try the indirect function

http://www.cpearson.com/excel/indirect.htm

VBA

[SteveG]

Try,

=INDIRECT(A1&"!F5")

HTH,

Steve

[arioch10]

Thank you both, that was exactly what I was looking for!

[Bryan Hessey]

Hi,

Is it possible to use the text in a cell as part of a formula or function in another cell. Say I'm in a sheet called TEST and cells A1, B1, and C1 look like this:

BLUE
GREEN
RED

and I have several different worksheets, 3 of which are named BLUE, GREEN, and RED. Normally if I wanted to link to the data in cell F5 in one of those sheets, the formula would look like:
=+'BLUE'!F5

My question is, is there someway I can use the data in cell A1 of my current sheet to tell the formula to look at sheet BLUE instead of actually typing BLUE or having to click over to that sheet while writing the formula. I know the following formula is invalid but this may help understand what I am trying to do.
=+'(A1)'!F5 or something along those lines instead of =+'BLUE'!F5

Thanks for your help!
Jeff

.. ..

of course,

you need something to change A1 from Blue to Green to Red, rather than use A1 B1 and C1 in your indirect formula.

---