What can stop a cell from being marked "Dirty"?

I have a formula that will not pull a value from another open workbook when it should. If I do a "Range.Recalculate" on the formulas it still doesn't pull the value (which I interpret to mean that it's not Dirty), but if I put my cursor into the formula bar and hit "Enter" it then recalculates and pulls the value from the other workbook (so I know the formula is correct).

I know that one of the precedents is being replaced by a macro, so I feel confident that the formula should be marked Dirty.

The only thing I can think of is that the precedent being marked Dirty is in the "Value_If_False" portion of the formula, but I can't figure out why that makes a difference and I can't figure out how to test that.

It's $M38 in this formula that is being replaced with a # by a macro and the formula returns 0.

Please Login or Register  to view this content.

If the macro replaces $L38, then the formulas pulls the correct value from the other workbook.

If formulas only evaluate the true part. why does changing a value in the false part require a recalculation?

I don't understand your question.

My macro replaces one of 2 cells ($L38 or $M38) with a #. The formula checks to see if $L38 was changed and if it wasn't then it checks to see if $M38 was changed, and if it wasn't then it returns 0.
My thinking is that when my macro changes $M38 Excel should be marking the formulas as Dirty and recalculating it, but it's not (I think).
When my macro changes $L38 the formulas gets recalculated (which I assume means that it's marked Dirty).

the false part does not get calculated at all so why should the formula have to be recalculated if you change that? note: if the false part contains a volatile function then the cell will be flagged as volatile.

Why doesn't the false part get calculated?
If the True part is not true, doesn't the False part automatically get calculated?

I think I read too much into your first post. I thought you were saying that the L38 part was true. if it isn't then the rest should be recalculated under normal circumstances. from the posts of yours I've read, I suspect the circumstances are not normal. is there a UDF involved that you have used 'volatile false' in?

There are no UDFs.

I'd need to see the problem then I reckon

I can't post the workbooks, they don't belong to me.
I'm hoping that someone knows of a reason that might cause a formula to not be marked Dirty.

try replacing the part of the formula called when m18 is true with a udf that prints something to the immediate window. at least that way you can be sure if it's being called at all.

Nice idea.

I haven't figured the whole problem yet, but I think the formula somehow confuses Excel when it tries to build the dependency tree.
There are no UDFs in the formula, but it's easier to explain how I "fixed" the problem if I use UDFs.

I simplified the formula in post # 1, because I thought it would be confusing to put the actual formula in the post. I'm still simplifying it to show my "solution".

The original formula that gave the wrong result when $M38 was changed:

Please Login or Register  to view this content.

I added these 2 cell formulas:

Please Login or Register  to view this content.

then changed my formula to:

Please Login or Register  to view this content.

and now it works fine.

Because $N38 and $O38 are also dependent on $L38 and $M38 respectively it confused Excel when building the dependency tree. Excel determined that it had to calculate $N38 before calculating the formula, but it could not determine that it had to calculate $O38 before the formula. I'm assuming that it's because it was part of the 2nd "IF" in the formula.

By putting the 2 UDFs into separate cells then letting the formula pick a cell result instead of a UDF to run, Excel could determine that it had to calculate both $O38 and $Q38 before calculating the formula.