=INDEX('XYZpayWS'!F9:F63,MATCH(InputWS!E10,'XYZpayWS'!A9:A63,0))
The user enters a selected value in a Sheet called InputWS, in cell E10
Based on the value entered XYZpays and InputWS will change to that value.
Example:
User Selects SnowWhite in Worksheet call InputWS in Cell E10
The index match formula would change to:
=INDEX(Snowwhite!F9:F63,MATCH(SnowWhite,'SnowWhite'!A9:A63,0))
Note: SnowWhite is selected by the end user from a Data Validation list. The list consist of the names of the possible worksheets.
Tried INDIRECT, CONCAT
1
INDIRECT is the only solution here:
=LET(
t,InputWS!E10,
INDEX(INDIRECT(t&"!F9:F63"),MATCH(INDIRECT(t),INDIRECT(t&"!A9:A63"),0))
)
The INDIRECT function converts a text into the reference. So, you need to compose the reference you needed in the text form, then apply INDIRECT to get the related reference.
The LET function allows to evaluate “InputWS!E10” to “t” and then use this value in next calculations. Without LET we need to repeat “InputWS!E10” in every place where it needs:
=INDEX(INDIRECT(InputWS!E10&"!F9:F63"),MATCH(INDIRECT(InputWS!E10),INDIRECT(InputWS!E10&"!A9:A63"),0))
0