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!

 

[Norie]

What is the value in A2 and what is it supposed to refer to?

What should $A$2 & Defect refer to?

 

[grigby21]

What is the value in A2 and what is it supposed to refer to?

What should $A$2 & Defect refer to?

A2 is a dynamic value. EX: A2 = 'con' , when concatenated with Defect, refers to Defined Name = conDefect.

 

[Aladin Akyurek]

INDIRECT("'"&$A$2&"'!$H$3:$H$99955")

implies that you have a sheet whose name equals the value in A2. Is that the case?

 

[grigby21]

INDIRECT("'"&$A$2&"'!$H$3:$H$99955")

implies that you have a sheet whose name equals the value in A2. Is that the case?

That is correct, and that is working properly. It's the reference to the Defined Name which is not working.

 

[Aladin Akyurek]

That is correct, and that is working properly. It's the reference to the Defined Name which is not working.

Thus we have A2 = con and A2&"Defect" which is a defined name. If so, and the same holds for A2&"UAT", then we should have:

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

where the contents of the defined names are used as conditions.


Is this your intent?

 

[grigby21]

Thus we have A2 = con and A2&"Defect" which is a defined name. If so, and the same holds for A2&"UAT", then we should have:

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

where the contents of the defined names are used as conditions.


Is this your intent?

Exactly!

 

[grigby21]

Exactly!

...however the approach noted, doesn't provide the expected results.

 

[Aladin Akyurek]

...however the approach noted, doesn't provide the expected results.

It was an attempt at intention reading...

Note that

INDIRECT($A$2 & "UAT")

must be a named range, that is, conUAT which stands for a range housing items that must hold for con!$H$3:$H$99955

and that

INDIRECT($A$2 & "Defect")

must be a named range, that is, conDefect which stands for a range housing items that must hold for con!$G$3:$G$99955.

If this re-construction does not reflect the situation you are in, you need to elaborate.

 

[grigby21]

It was an attempt at intention reading...

Note that

INDIRECT($A$2 & "UAT")

must be a named range, that is, conUAT which stands for a range housing items that must hold for con!$H$3:$H$99955

and that

INDIRECT($A$2 & "Defect")

must be a named range, that is, conDefect which stands for a range housing items that must hold for con!$G$3:$G$99955.

If this re-construction does not reflect the situation you are in, you need to elaborate.

Thanks. I do have Defined Names created for conUAT, and conDefect... and when they are entered directly into the formula, it works fine. When I try to add the concatenated value, it is seemingly not recognized as a Defined Name.