Excel 2007 : Pull data from a sheet name referenced by a cell

Hey all,
Here is my setup
I have a main sheet with several pieces of data. In Cell A2 I have a persons name, we'll call him Kent

I have other sheet names called "Kent 6 Mo Currency" and "Kent 12 Mo Currency"
What I am trying to do is: in Cell P2 on the main sheet, I want to reference cell H5 in "Kent 12 Mo Currency" using A2 as a variable.
I've tried :
=INDIRECT(CONCATENATE(A2," 12 Mo Currency!H5"))
=INDIRECT(A2&" 12 Mo Currency!H5")
They both return #REF

I am trying to work it this way so that when I copy the formula to the next person, i won't have the pain of typing "=" then selecting the sheet name and cell


I would like the format to become cell A2 would be "Kent, Clark" and the formula would pull from sheet "Clark 12 Mo Currency"

Can anyone offer suggestions?

Try:

=INDIRECT("'"A2&" 12 Mo Currency'!H5")

if A2 contains Kent, Clark

=INDIRECT("'"&TRIM(MID(A2,FIND(","A2)+1,255))&" 12 Mo Currency'!H5")

Originally Posted by usafmeinweg

That didn't work, however i did get this to work
=INDIRECT(CONCATENATE("'",A2," 12 Mo Currency'!H5"))
Thanks

anyone know how I can now make it so the persons name can be Kent, Clark and it reference Sheet "Kent 12 Mo Currency"?

Sorry forgot an &

should've been: =INDIRECT("'"&A2&" 12 Mo Currency'!H5")

for your next question:

=INDIRECT("'"&LEFT(A2,FIND(",",A2)-1)&" 12 Mo Currency'!H5")