Dynamic Sheet Tabs in Index Match function

[PeterDavids]

Hi All

I am trying to use the index match function in a formula in Sheet "Account Tracker" to return the opening balance contained in another tab, "ABSA 0860" of my workbook.
I want the sheet tab to be dynamic in my formula.

The formula is in a sheet named "Account Tracker" where the formula is "=INDEX(INDIRECT("'"&$A4&"'!$B$5:$B$5000"),MATCH(C$1,INDIRECT("'"&$A4&"'!$A$5:$A$5000"),0))"
The formula results in an error. (#REF!)

KEY OF THE FORMULA:
Cell: A4 = Tab name of the target sheet in Account Tracker Sheet, eg. ABSA 0860
Range B5:B5000 = Opening Balances in tab ABSA 0860
Cell C1 = Target date in Account Tracker Sheet
Range A5:A5000 = Date in tab ABSA 0860

Any help in correcting the formula will be appreciated.
Thanks
Peter

 

[MARK858]

Try...

=INDEX(INDIRECT("'"&$A4&"'"&"!$B$5:$B$5000"),MATCH(C$1,INDIRECT("'"&$A4&"'"&"!$A$5:$A$5000"),0))

,
Which I think is basically the same as you posted, which works for me. Check your sheet name is correct in both the tab and the cell (especially the spaces [including leading and trailing spaces])

 

[PeterDavids]

Thank you, it was the space in my sheet name that was the problem.

Could you please check if this formula is correct, as it gives me a #VALUE! error?
=SUMIFS(ABSA0860!$T$5:$T$5000,ABSA0860!$A$5:$A$5000,"<="&AK$67,ABSA0860!$A$5:$A$5000,">="&AK$66)

Many thanks.

 

[PeterDavids]

Sorry this is the formula in question.

=SUMIFS(INDIRECT("'"&$AJ229&"'"&"!$D$5:$D$5000",INDIRECT("'"&$AJ229&"'"&"!$D$5:$D$5000","<="&AK$67,INDIRECT("'"&$AJ229&"'"&"!$D$5:$D$5000",">="&AK$67))

 

[MARK858]

Thank you, it was the space in my sheet name that was the problem.

Happy you got it sorted

 

[Peter_SSs]

Sorry this is the formula in question.

=SUMIFS(INDIRECT("'"&$AJ229&"'"&"!$D$5:$D$5000",INDIRECT("'"&$AJ229&"'"&"!$D$5:$D$5000","<="&AK$67,INDIRECT("'"&$AJ229&"'"&"!$D$5:$D$5000",">="&AK$67))

No, that isn't the formula giving a #VALUE! error as that is not a valid formula. (You have also changed that last AK$66 to AK$67 which would make it something of a strange formula even if it was valid.)
You have quite a few parentheses missing or in the wrong places.
See if this is what you are after

=SUMIFS(INDIRECT("'"&$AJ229&"'"&"!$D$5:$D$5000"),INDIRECT("'"&$AJ229&"'"&"!$D$5:$D$5000"),"<="&AK$67,INDIRECT("'"&$AJ229&"'"&"!$D$5:$D$5000"),">="&AK$66)

 

[PeterDavids]

Thank you so much it sorted my problem, with a couple of range updates in the formula.
BTW: AK$66 refers the 1st day of the month and AK$67 refers to the last day of the month.
So I am summing between these two dates.

 

[Peter_SSs]

So I am summing between these two dates.

So, yes, you would need some range changes as the only range mentioned in your formula in post 4 was column D.
Anyway, glad you got it sorted.

I think that you should check which post you have marked as the solution though. What should be marked is the solution to your original question, which I don't think my post is.

 

[PeterDavids]

Done! and thank you once again.

 

[Peter_SSs]

Cheers, that looks better.