crazy problem I've never seen

dhx10000 said:

Hello,

I have a range of cells that are designated as a list. Directly to the right of this list I have created 3 columns that have formulas that calculate data based on each row. The problem is two of these 'calculated data' columns contain a #VALUE error when I click inside the list. When I click on one of the actual calculated data cells the #VALUE error disappears and the correct results of my formulas appear as I want them. I thought this was just a visual glitch but I just tried to make a pivot table from this data and can't do it because the #VALUE errors are also being read by my pivot table.

Click to expand...

Are you talking about a list created with Data|List|Create List? And, what are the formulas that tend to return #VALUE! errors?

Yes, that is the list I mean.

Here are the two formulas that are doing this:

Column Z
=IF(F2<>"",SUMPRODUCT(--($C$2:$C$5305=C2),--($F$2:$F$5305=F2),--($Y$2:$Y$5305=Y2)),"")

Column AA
=IF(AND(Z2<>"",Z2>0),IF(AND(Z2=1,X2="Closed"),T2-U2,IF(AND(Z2=1,X2="Open",V2=TRUE),TODAY()-U2,IF(AND(Z2=1,X2="Open",V2=FALSE),T2-U2,IF(AND(Z2>1,V2=TRUE),0,IF(AND(Z2>1,X2="Open",V2=FALSE),T2-U2,IF(AND(Z2>1,X2="Closed",V2=FALSE),T2-U2,"ERROR")))))),"")

these are then copied down using the option 'Fill down formulas in columns adjacent to data'

dhx10000 said:

Yes, that is the list I mean.

Here are the two formulas that are doing this:

Column Z
=IF(F2<>"",SUMPRODUCT(--($C$2:$C$5305=C2),--($F$2:$F$5305=F2),--($Y$2:$Y$5305=Y2)),"")

Column AA
=IF(AND(Z2<>"",Z2>0),IF(AND(Z2=1,X2="Closed"),T2-U2,IF(AND(Z2=1,X2="Open",V2=TRUE),TODAY()-U2,IF(AND(Z2=1,X2="Open",V2=FALSE),T2-U2,IF(AND(Z2>1,V2=TRUE),0,IF(AND(Z2>1,X2="Open",V2=FALSE),T2-U2,IF(AND(Z2>1,X2="Closed",V2=FALSE),T2-U2,"ERROR")))))),"")

these are then copied down using the option 'Fill down formulas in columns adjacent to data'

Click to expand...

Does this:

=IF(N(Z2),IF(V2,(TODAY()-N(U2))*(X2="Open")+(N(T2)-N(U2))*(X2="Closed"),N(T2)-N(U2)),"Error")

cover the longer IF you invoke and eliminate the error-value you sometimes obtain?

I did some more troubleshooting on this problem I'm having. The problem is that I have 2 columns of calculated data that is adjacent to a list (data>>list>>create list) that appears when I click on the actual cell that contains the calculated data but shows a #VALUE error when I click on the data within the list. I have little green arrows appearing on every cell of calculated data cells. When I click on the arrow a drop down menu appears that says 'formula refers to empty cells'.

It is true that some of the cells that my calculated formula refers to contains empty cells but this problem still isn't making sense. Especially because this strange problem goes away if I hardcode values into the formula:

=IF(F53<>"",SUMPRODUCT(--($C$2:$C$5305=C53),--($F$2:$F$5305=F53),--($Y$2:$Y$5305=Y53)),"")

Column F is a numeric column that contains numbers, but does have some blank cells

Column C is also a numeric column with no blanks cells.

Column Y is a 'general' column that has no blank cells.