Circlular Reference Error

[missit]

Hi,

I am working on a workbook that currently has 3 worksheets, one is the summary and the other two contain the data that I am searching on.

the following formula works and provides me the number of times that a # is used correctly on the two data sheets. However, when I change the formula to find the number of incorrect responses by change the "Y" to "N", I get a circular reference error.

Names is the defined name of the sheet that has the listing of worksheet names.

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&Names!$A$1:$A$4&"'!D:D"),$A3,INDIRECT("'"&Names!$A$1:$A$4&"'!F:F"),"Y"))

Column D on the data sheets contain the different codes I am matching to A3 and Column F has either a Y or N.

thanks for any assistance.

Carole

[:) Sixthsense :)]

"Y" is a criteria of Countif() and it is not the actual reason for creating Circular Reference

[missit]

Hi, I know that it is the criteria, but the formula works in column C of the Main worksheet with the Y, but when I copy the formula to Column D on the Main worksheet and change the "Y" to a "N" I get the error. Could it be looking at column D on the Main worksheet instead of the two data sheets?

Note:

Column B on the Main worksheet has this formula:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Names!$A$1:$A$4&"'!D:D"),$A3)) which gives me the total number of times that a particular code is used on both of the other worksheets.

Thanks again for trying to help.

[:) Sixthsense :)]

Because the name of the sheet on which the formula is applied should be there in this range $A$1:$A$4

[missit]

Hi,

Sorry, I am not following what you mean. Names is the defined name of the listing of the worksheets in the workbook.

Carole

[:) Sixthsense :)]

Just confirm me what resides in Names!$A$1:$A$4 cells?

[missit]

the names of the worksheets in the workbook.

[:) Sixthsense :)]

the names of the worksheets in the workbook.

Whether it includes the name of the sheet on which you enter the formula?

[missit]

You found it. --- thank you

Carole

[missit]

I will mark this solved if you can tell me how. I haven't been in the forum in awhile and don't remember how.

[:) Sixthsense :)]

Please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[missit]

thanks again. really appreciate the quick turn around.

Carole