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.
{=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.