Add text of one cell to formula in another cell

[ford.cunningham]

I have look at a lot of different post and can not find one that addresses this issue.
I have a column that each cell in the column is a text value that matches the name of a sheet in the work book.
So A1 has a text value of Sheet1, A2 has a text value of Sheet2, A3 has a text value of Sheet3 and so on. In cell A6 of that sheet i want to put a formula like ='Sheet1'!H11 to reference cell H11 on the sheet in the formula. My issue is i do not want to explicitly have to type in the sheet name for each one. I was hoping to find a way to reference the text value of cell A? in the formula something like =T(A1)!H11. Obviously this creates an error but i hope this explains what i am trying to accomplish.

Any ideas would be appreciated.

 

[T. Valko]

I have look at a lot of different post and can not find one that addresses this issue.
I have a column that each cell in the column is a text value that matches the name of a sheet in the work book.
So A1 has a text value of Sheet1, A2 has a text value of Sheet2, A3 has a text value of Sheet3 and so on. In cell A6 of that sheet i want to put a formula like ='Sheet1'!H11 to reference cell H11 on the sheet in the formula. My issue is i do not want to explicitly have to type in the sheet name for each one. I was hoping to find a way to reference the text value of cell A? in the formula something like =T(A1)!H11. Obviously this creates an error but i hope this explains what i am trying to accomplish.

Any ideas would be appreciated.

Like this...

A1 = Sheet1

=INDIRECT("'"&A1&"'!H11")

Which resolves to: ='Sheet1'!H11

 

[Mindpsyche]

I have look at a lot of different post and can not find one that addresses this issue.
I have a column that each cell in the column is a text value that matches the name of a sheet in the work book.
So A1 has a text value of Sheet1, A2 has a text value of Sheet2, A3 has a text value of Sheet3 and so on. In cell A6 of that sheet i want to put a formula like ='Sheet1'!H11 to reference cell H11 on the sheet in the formula. My issue is i do not want to explicitly have to type in the sheet name for each one. I was hoping to find a way to reference the text value of cell A? in the formula something like =T(A1)!H11. Obviously this creates an error but i hope this explains what i am trying to accomplish.

Use the top example, mine was incorrect.

 

[pplstuff]

Look into the indirect() function.

Something like:
=indirect("'"&a1&"'!h11")

You might have to play with it a little for your desired result.

 

[cobwebid]

Like this...

A1 = Sheet1

=INDIRECT("'"&A1&"'!H11")

Which resolves to: ='Sheet1'!H11

I registered just to say thank you very much! I am not an expert in Excel and was having a problem.
Finally the desired output: =COUNTA(INDIRECT("'"&$B2&"'!A$1:A$100"))