Combining two cells with text to create a formula

[Jdax57]

I'm trying to combine two text cells to form a usuable formula:

A42: "=DATA!H"
B42: 695

C42: I want the outcome to be: =DATA!H695 and for it to be a formula that pulls the data correctly from Data sheet Cell H695.

I've tried: =A42&B42 and =indirect(A42)& INDIRECT(B42)

A42 does not change, B42 changes in value dependent on a different formula.

Anyone have any ideas?

I appreciate any help.

 

[Jonmo1]

It would be

A42: DATA!H
B42: 695

=INDIRECT(A42 & B42)


Do you need to vary the Sheet Name, or is the sheetname always Data?
If it's always the same, there's no need for Indirect

=INDEX(DATA!H:H,B42)

Hope that helps.

 

[Jdax57]

jonmo....the index equation was exactly what I needed. Thanks for the help!