Indirect Index and Match issues

[cobra30md]

Hello, I've searched the board and many other sites but I can not get this formula to work. I have a formula which runs well but am trying to make it dynamic so all I need to do is change the name(month) of the sheet I want to evaluate and I'm ready to roll. The formula below is what I use now but it is pretty hardcoded.

{=INDEX(May21Pivot!$G$1:$G$500,MATCH(1,('May21'!$B3=May21Pivot!$B$1:$B$500)*('May21'!$C3=May21Pivot!$C$1:$C$500)*("Activity"=May21Pivot!$D$1:$D$500),0))}

I've tried indirect but can never get it to work correctly for this. I always end up with some type of error.

 

[kweaver]

Try the following. I put this in a "master" sheet with the month and year in B7 in the form of: 'June21' for example.
Oh, you can drag this because of the cell references inside the INDIRECT. There are ways around that, but continue as a pain, frankly.

=INDEX(INDIRECT(B7&"Pivot!$G$1:$G$50"),MATCH(1,(INDIRECT(B7&"!$B3")=INDIRECT(B7&"Pivot!$B$1:$B$20"))*(INDIRECT(B7&"!$C3")=INDIRECT(B7&"Pivot!$C$1:$C$20"))*("Activity"=INDIRECT(B7&"Pivot!$D$1:$D$20")),0))

Array Formula needing Ctrl+Shift+Enter

If you want to try to get around fixing the cell reference, look at ADDRESS:

This yields: $C3

 

[cobra30md]

Got it. Thanks for your help. I did end up incorporating the ADDRESS as well into it. Works great now.