Chart always complains about invalid references - Excel 2007

Question

I made a XY plot that shows points from one data set in two different colors, depending on a set of conditions. I achieved this by making the source table three columns instead of two. First column is the X. Second column is Y is one set of conditions apply, third column is Y is the other set of conditions apply. So the second and third columns have formulas like this in them, respectively:

=IF(ConditionApplies,YValue,"")
=IF(ConditionApplies,"",YValue)

(So the graph actually has two series, each of which is not a contiguous block of numbers - each is interspersed with "nothing")

When I make a change that affects the ConditionApplies, the table reacts properly. Then I switch to the chart (on a different sheet) and it always says: "A formula in this worksheet contains one or more invalid references...". Click OK.

The chart itself always looks the way I would expect, with two different sets of points according to the Conditions I devised. If I inspect the data source fields, all the references are intact and proper.

Basically everything works, I would just like to avoid this annoying pop-up.

Thanks!

Answer 1

I 100% understand everything you've said here and, on the surface, it sounds like it's not any kind of bug. It seems like you are actually referencing something you shouldn't. If that's, in fact, the case that's obviously something you want to fix.

My first guess would be to look at your "ConditionApplies" formulas. Under certain cases, would they create invalid references (referencing data of the wrong type, dividing by zero, circular references, etc.). The most common cause of problems like that would be dragging formulas but not having the "$" signs in the appropriate places. So your cell references change when you expected they'd stay the same.

For example:

=SUM(A1:G25)

should be something like the following to prevent the column and row from incrementing when dragged:

=SUM($A$1:$G$25)

Recommendation

Look at the "ConditionApplies" formulas (or better yet, post them here) and aggressively place $ where ever they don't break things. Then "re-drag" your new formulas, updating the previous ones.

Answer 2

See comment under gmale's answer