Sheet name in formula

[ksgoodwin]

I have a column (A1:A10) of sheet names (sheet2, sheet3, etc) on
sheet1. I want to put in column (B1:B10) of sheet1 values from each
sheet on the workbook named in column A from say cell H1. Is there an
easy way write a formula and copy / paste? I can hard wiring it; but
I have a lot of sheets to copy / paste.

Ken

 

[JE McGimpsey]

One way:

B1: =INDIRECT("'" & A1 & "'!H1")

 

[Aqib Rizvi]

If you do not want to keep the indirect formula in worksheet for any
reason, try this.

Write formula ="="&A1&"!"&"H1" in cell B1 and copy it upto B10.
Then select B1:B10 and copy, stay at same place, and click Paste
Special, Values, Okay
Select B1:B10 and click Data, Text to Columns, Finish.

Aqib Rizvi