One way:

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

In article ,
Pedro Serra wrote:

In an excel file with multiple sheets (for instance sheets names A and B) is
it possible to use in sheet A in cell A2 a formula like =A1!&A2, where A2 is
a in sheet B and A1 is in sheet A and as the tab name (B) written. The
formula should return the value in cell A2 of sheet B.