MATCH function within INDIRECT function not working

[mgerada]

Hi all excel gurus

I have hit a brick wall and would appreciate some input.

I have a formula in a worksheet called Trading FY1011, which returns the interest for a particular financial year once a match occurs in an interest worksheet called INTEREST FY1011.

In Trading FY1011, cell A1 = FY1011 which is returned from the sheet tab name ie Trading FY1011.

The current formula which is specific to one worksheet works a treat:
=INDIRECT("'INTEREST FY1011'!B"&MATCH(J5,'INTEREST FY1011'!$A$1:$A$50)).

What I want to achieve is to make the formula generic so that as new trading worksheets are created for future financial years the formulas reference cell A1 which will update to reflect the current sheet financial year status eg FY 1112, FY 1213 etc.

I have managed to modify the INDIRECT component of the current formula. So when I substitute
"'INTEREST FY1011'!B" with "'INTEREST"&" "&A1&"'!B" it works well as in the second formula below ie.
=INDIRECT("'INTEREST"&" "&A1&"'!B"& MATCH(J5,'INTEREST FY1011'!$A$1:$A$50)).

However when I try to modify the second element of the MATCH function I hit the snag. When I substitute 'INTEREST FY1011'!$A$1:$A$50 with "'INTEREST" & " " & A1 & "'!" &"$A$1:$A$50" as in the formula below:
=INDIRECT("'INTEREST"&" "&A1&"'!B"&MATCH(J5,"'INTEREST" & " " & A1 & "'!" &"$A$1:$A$50")) it fails.
Can anyone highlight if there is something missing or is the second element of the MATCH function not receptive to the referenced data in cell A1.

Many thanks for any insights.

Cheers
mgerada

[canapone]

Hi,

you could try to add INDIRECT inside MATCH references.

=INDIRECT("'INTEREST"&" "&&A1&"'!B"&MATCH(J5,INDIRECT("'INTEREST"&" "&&A1&"'!" &"$A$1:$A$50")))

Regards

[mgerada]

Hi Canapone

It works!! Well done. Very elegant.

Many thanks for your help.

mgerada