Formula Help - Replace Sheet Name with dynamic cell reference

[benposaner]

Hi All

I'm sure it's a really simple and obvious solution, but I've tried a few obvious (to me) ideas, but keep on getting errors.

I've got the following formula and it works fine.
=IFERROR(OFFSET('Data Sheet 1'!$A$1,XMATCH($A4,'Data Sheet 1'!$A$2:$A$4),XMATCH(B$3,'Data Sheet 1'!$C$1:$F$1)+1),"No Data Available")

But I want it to be dynamic and replace the 'Data Sheet 1' parts with a cell reference, which will contain the sheet name.

Any help would be much appreciated.

Thanks

 

[JEC]

You need an INDIRECT

 

[Fluff]

How about like

=IFERROR(OFFSET(INDIRECT("'"&A1&"'!$A$1"),XMATCH($A4,INDIRECT("'"&A1&"'!$A$2:$A$4")),XMATCH(B$3,INDIRECT("'"&A1&"'!$C$1:$F$1"))+1),"No Data Available")

 

[benposaner]

You need an INDIRECT

I did try that, but could not figure how it should be coded, with the ! etc.

This gives me errors
=IFERROR(OFFSET(INDIRECT(A1)&!$A$1,XMATCH(........

I'm sure I need some kind of '' and &s, but I can't figure it out!

<UPDATE> Just saw the above. Thanks.

 

[Fluff]

Glad we could help & thanks for the feedback.