Indirect funcion and tab name

[Leviatano80]

Hi, I hope that you can help with this.
I use Indirect function to calculate sum if I mees specific conditions. =SUMPRODUCT(SUMIFS(INDIRECT("'"&$Y$1:$Y$34&"'!j2:j103");INDIRECT("'"&$Y$1:$Y$34&"'!e2:e103");'Global Forecast'!$E2))

​

using F9 and selecting only this part of formula SUMIFS(INDIRECT("'"&$Y$1:$Y$34&"'!j2:j103");INDIRECT("'"&$Y$1:$Y$34&"'!e2:e103");'Global Forecast'!$E2) I am able to see all the elements of my matrix {0;0;0;0;0;0;0;22;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0} but for me it would be more useful to see the name of the TAB so that I can understand from which tab we have a value. in this case I should check that value 22 comes from tab 8.
any solution?
thank you

 

[jasonb75]

You wouldn't be able to see it at that point but you might be able to by adding some extra steps (please note that I haven't tested this, it is a theory that should do what you need).

The tab names will come up about 4 steps after the array that you have shown in your post.

=LET(what;SUMIFS(INDIRECT("'"&$Y$1:$Y$34&"'!j2:j103");INDIRECT("'"&$Y$1:$Y$34&"'!e2:e103");'Global Forecast'!$E2);where;IF(what;$Y$1:$Y$34;"");SUMPRODUCT(what))