Dynamic 'Defined Name' values within a formula not providing expected results

[grigby21]

Have a formula:
=SUMPRODUCT(COUNTIFS( INDIRECT("'"&$A$2&"'!$H$3:$H$99955"), $A$2 & UAT, INDIRECT("'"&$A$2&"'!$G$3:$G$99955"), $A$2 & Defect))

WHERE $A$2 refers to a dynamic value which serves as a prefix in the Defined Name naming convention.

Results being returned are not in agreement with the static search criteria.

Thoughts?

Thanks!

 

[Aladin Akyurek]

Thanks! ...though unable to access for some reason. What I did learn, is that it seems the definition of my Defined Names is not being handled properly. When I changed the 'Refers To' from: =OFFSET('Mapping - Defects'!$O$7,0,0,COUNTA('Mapping - Defects'!$O$7:'Mapping - Defects'!$O$23),1) to an explicit definition: ='Mapping - Defects'!$O$7:$O$13 ...the formula worked. Sigh.

I'm not convinced...

Once more: https://www.dropbox.com/s/yl643jxv0h0jiyh/grigby21%20sumproduct%2Bcountifs%2Bindirect.xlsx?dl=0

If you define conUAT and conDefect via the Name Box, the relevant INDIRECT bits involving these names will succeed.

By the way, a name definition using OFFSET precludes running INDIRECT on that name.


Also, thanks for the update.

 

[grigby21]

I'm not convinced...

Once more: https://www.dropbox.com/s/yl643jxv0h0jiyh/grigby21 sumproduct+countifs+indirect.xlsx?dl=0

If you define conUAT and conDefect via the Name Box, the relevant INDIRECT bits involving these names will succeed.

By the way, a name definition using OFFSET precludes running INDIRECT on that name.


Also, thanks for the update.

Thank you for your help! ...still no luck accessing link; will try from home later today. Sigh. So I suppose I'll be reworking my Defined Name definitions... all 160. haha...

 

[Aladin Akyurek]

Thank you for your help! ...still no luck accessing link; will try from home later today. Sigh. So I suppose I'll be reworking my Defined Name definitions... all 160. haha...

What does dropbox say?

 

[grigby21]

"This page cannot be displayed"

 

[Aladin Akyurek]

"This page cannot be displayed"

I see. Dropbox wants me to pay!.. If you like, drop me a line with your email via a private message.