Formula dependency order (precedents) not working as expected (Excel bug?)

[lsmith999999]

Hi there.

This is a bit long but it's not that complicated (really) and I've been struggling with it for more than a week now (though I'm not that experienced in Excel or VBA but am an experienced developer otherwise). I've built a relatively simple workbook but am having a confounding problem with formula dependencies (precedents) across sheets. It appears to be an Excel bug (or a serious misunderstanding on my part) but it's not clear yet.

I have 12 sheets named "Jan" to "Dec" in left-to-right order. Each sheet appears as follows (showing "Apr" for this example):

Please Login or Register  to view this content.

The value 3 above is a just a count of all columns to the left of the "Totals" column (column X) containing the letter 's'. The value 17 in brackets is then just a running total for that row from all previous sheets ("Jan" to "Mar"), including the above sheet itself ("Apr"). The formula I use in the "Totals" column is this:

=MyCountIf(A1:W1, "s", Mar!X1)

The first arg is the range to search for occurrences of 's' on the current row, where 's' is passed via the second arg, and the last arg is the address of the corresponding "Totals" cell on the *previous* sheet ("Mar" in this case). The function simply counts the occurrences of 's' on this row (A1:W1 passed via the first arg), yielding the count for April (3 as seen), and then strips the value between the brackets from the "Totals" cell on the *previous* sheet (cell Mar!X1, not shown above but containing, say, "5 (14)" for this example), resulting in 14. It then adds 3 + 14 = 17 and returns this as "3 (17)", populating the above sheet ("Apr") with this.

For this technique to work however, Excel *must* evaluate each formula in order from Jan to Dec. It should AFAIK since formulas are updated based on changes to their dependent ("precedent") args (from what I've read but it only makes sense anyway). Therefore, since the above formula for "Dec" is dependent on "Nov" which is dependent on "Oct" and so forth all the way back to "Jan" (based on the 3rd arg to my function), if I add or delete an "s" on this row for any month (sheet), the value in the "Totals" column (column X) should be updated on each subsequent sheet and in month order (from left-to-right starting with the sheet I update).

Unfortunately, this doesn't seem to be case. Sometimes it does (do it in month order), but mostly it doesn't. The order is random and inconsistent. My function is very simple though, with no obvious errors, and shouldn't normally impact the dependency order that Excel uses to call it anyway (which I don't control of course). It should *always* be called in month order AFAIK since each month's formula is dependent on the "Totals" cell of the previous month (again, due to the 3rd arg)

The upshot is why might this be occurring. Shouldn't the dependency order Excel uses (given the 3rd arg I'm passing) always cause it to update the "Totals" column on each sheet in month (left-to-right) order?

Lastly, note that I've tried many things including rebuilding the workbook itself but to no avail. I've also tried combining the args A1:W1 and Mar!X1 into a single arg by bracketing them and relying on "Range.Areas" in my function. It appears this is illegal however, likely because they're targeting two different sheets (can anyone confirm this since targeting a single sheet does work). I'm using the latest version of Excel at this writing (16.X).

Thanks for your help (appreciated)

[CK76]

I'd recommend uploading sample workbook with desensitized data.

That would help us in pinpointing cause of your issue.

[lsmith999999]

Ok, thanks. See stripped-down sheet attached. The "Totals" column in my previous (contrived) example is actually called "Sick days" on the real (attached) sheet (the other columns to its right not working yet so just ignore them). Add or delete an 's' in any of the numbered day-of-month columns on row 4 (only row 4 working for now) and the "Sick days" column on row 4 will be updated with the count of 's' on that row (as previously described). Now look at the other month sheets which follow. As previously described, they don't always get updated in the correct (expected) month order so the year-to-date total in brackets in the "Sick days" column is often wrong (because it's updated based on the previous sheet which isn't being updated in the correct order). The VBA code is included of course (it's an ".xlsm" file).

Thanks again.

[CK76]

Oh I see...

Your function, CountIfCode must be made volatile.

It's because UDF is only re-evaluated on Worksheet_Change, of the range(s) specified in the argument.

Meaning... =CountIfCode(C4:AB4, "s", Mar!AC4) will change if C4:AB4 of 'Apr' sheet is changed, but will not update if you change value in Mar!C4:AC4 range.

Values changed via formula will not trigger Worksheet_Change event.

Add below line at top of your CountIfCode.

Please Login or Register  to view this content.

NOTE: In most cases, I'd advise against this practice. As UDF tend to be slower than standard function and can add large overhead to worksheet performance (depends on how many there are, and on depth of dependency etc)

[lsmith999999]

Thanks for the feedback. I played with "Application.Volatile (True)" last week based on an earlier design and abandoned it for the reasons you cited (once I discovered it). It's grossly inefficient. However, it doesn't change the situation even for the current design. Try it. It doesn't correct things. That's why I started passing the dependent ranges to "CountIfCode()" based on what I read from MSFT (that formulas are updated based on their args, which makes sense of course). It wasn't clear however if that meant *all* args though (if multiple args are passed), or those in from other worksheets. In theory you would think so. I don't know enough about Excel yet however but have been learning quickly (will now look into "Worksheet_Change" for instance). It's hard to understand how a formula wouldn't be updated based on *all* args regardless of their worksheet (on the surface it makes no sense assuming there's not some legitimate reason that escapes now right now).


It's unclear to me how to make this work. I've been spinning my wheels for a week+ now and it's hard to believe it's not doable. It seems a pretty fundamental requirement for many. Do you have any suggestions? I can think of some still but they're ugly (hacks) and I'm still not sure if it will work (and don't want to use them even if they do). Any ideas? Your help is certainly appreciated. Thanks again ...

[CK76]

Personally, I'd start with different mechanism for tracking (i.e. data storage).

Data should always be stored in flat table, and not in cross-tab structure. Data entry can be done via Data Form, or through UserForm.

Then report should be done via PivotTable using flat-table as source (for even more flexibility, I like to load data from flat table into OLAP data model).

With your current setup, downstream analysis and calculations would be unnecessarily complicated as you are finding out.

EDIT: Oh one more thing. Never use cell fill color (highlight) as sole mechanism to indicate some criteria. Always have underlying value (text or numeric) that can be used in formula, Conditional Format etc.

[lsmith999999]

Thanks for the feedback. I played with "Application.Volatile (True)" last week based on an earlier design and abandoned it for the reasons you cited (once I discovered it). It's grossly inefficient. However, it doesn't change the situation even for the current design. Try it. It doesn't correct things. That's why I started passing the dependent ranges to "CountIfCode()" based on what I read from MSFT (that formulas are updated based on their args, which makes sense of course). It wasn't clear however if that meant *all* args though (if multiple args are passed), or those in from other worksheets. In theory you would think so. I don't know enough about Excel yet however but have been learning quickly (will now look into "Worksheet_Change" for instance). It's hard to understand how a formula wouldn't be updated based on *all* args regardless of their worksheet (on the surface it makes no sense assuming there's not some legitimate reason that escapes now right now).


It's unclear to me how to make this work. I've been spinning my wheels for a week+ now and it's hard to believe it's not doable. It seems a pretty fundamental requirement for many. Do you have any suggestions? I can think of some still but they're ugly (hacks) and I'm still not sure if it will work (and don't want to use them even if they do). Any ideas? Your help is certainly appreciated. Thanks again ...

Sorry, I should just clarify that I do understand you're saying it's not being updated because it's dependent is being updated by a formula (and that event doesn't trigger based on a "recalculation" according to MSFT's docs). Will need to review this further and look into events. Wasn't aware of them in Excel until now though as an experienced C++ developer I get it (intimately). Maybe it's doable that way (using some event I can trap). Thanks.

[lsmith999999]

Personally, I'd start with different mechanism for tracking (i.e. data storage).

Data should always be stored in flat table, and not in cross-tab structure. Data entry can be done via Data Form, or through UserForm.

Then report should be done via PivotTable using flat-table as source (for even more flexibility, I like to load data from flat table into OLAP data model).

With your current setup, downstream analysis and calculations would be unnecessarily complicated as you are finding out.

EDIT: Oh one more thing. Never use cell fill color (highlight) as sole mechanism to indicate some criteria. Always have underlying value (text or numeric) that can be used in formula, Conditional Format etc.

Ok, thanks. We could debate the merits of my approach but the sheet is actually someone else's. I'm just trying to get this particular formula working for them. The design is clean though IMHO (I suggested adding the running totals over the summary sheet they're currently using) and they prefer it. Their needs are also very simple (trivial). Your suggestions would be too complicated for them and their ability to support it. So it's just a matter of how to code the running totals as you've seen.

In my (very long) experience however I'd expect that the way I'm doing it is the natural way and I'm (very) surprised it doesn't work. Anyone not experienced in Excel such as myself (and even many who are) would just naturally expect any changes to propagate without issue (across worksheets and by indirect recalculation). The fact that it doesn't work is counter-intuitive and potentially error-prone. Someone can easily code something thinking it will work, be fooled into thinking it works if their initial tests are successful, only to find out later that the dependencies aren't working as they thought (oops).

Anyway, I'll look into events to see if it can be handled that way somehow (unless you have some other uncomplicated idea). Thanks again. Appreciated!

[CK76]

If you can't change data structure.
I'd recommend setting up worksheets to have same column dimension. I. E. 31 columns for date columns.
Then split out total year to date in separate column from total for current month. Then you can use 3d reference and sum values from multiple sheets honoring precedents.

[lsmith999999]

If you can't change data structure.
I'd recommend setting up worksheets to have same column dimension. I. E. 31 columns for date columns.
Then split out total year to date in separate column from total for current month. Then you can use 3d reference and sum values from multiple sheets honoring precedents.

Thanks. Good idea and I even briefly thought along those lines after discovering 3d references last week (did some minimal reading on it). I think the technique I'm pursuing though is ergonomically better (a single column with "# (#") instead of two).

Note that there will also be multiple total columns with different codes (not just 's'), and at least 4 of them so 8 columns would be required. Not sure if 3d would be any easier or efficient anyway (unless you already know so) since totals still need to be summed from sheet-to-sheet preferably. I don't want to duplicate calculations of course (if possible), or have to recalculate the running totals from the start of the year for each month (when it's already available on the previous sheet). Unclear if this is all doable in 3d until I review it (not sure what its capabilities are).

You did clue me into events though (thanks) so I should be able to eliminate the 3rd arg to my function and just manually trap "Worksheet_Change" (updating the running totals at that time so I now have control over the order). Should have thought of it myself since the entire Excel object library is obviously COM (Component Object Model) based (if you're familiar with that). Much of the Windows ecosystem is and I have a lot of experience in it (but was so busy immersing myself in Excel and VBA the past week I didn't even think of it).

Anyway, still surprised this can't be done without turning to VBA. What I'm doing seems like such a simple task and the cells are being updated, just not in the correct order (which I still don't understand - not updating would be one thing, but they are so why isn't the order correct). Non-technical users of Excel (most likely are) would be stuck at this point (and have to turn to some other technique like 3d even if it's not their technique of choice).

[hrlngrv]

. . .
Meaning... =CountIfCode(C4:AB4, "s", Mar!AC4) will change if C4:AB4 of 'Apr' sheet is changed, but will not update if you change value in Mar!C4:AC4 range.
. . . Application.Volatile . . .
NOTE: In most cases, I'd advise against this practice. . . .

There's a hack which makes volatility optional.

Please Login or Register  to view this content.

Then the UDF could be called as =foo(a,b,. . .,h) or =foo(a,b,. . .,h,COUNTA(Mar!C4:AC4)). In the former, only changes which affect arguments a to h would cause the formula calling this UDF to recalc. In the latter case, the formula would recalc also if any cells in Mar!C4:AC4 changed. Excel is smart enough to know in the latter case that the formula depends on all cells in Mar!C4:AC4, but it's not smart enough to know that the UDF isn't doing anything with that last argument.

This trick can also be used when working with 3D ranges. The Excel-VBA interface doesn't support actual 3D references like Sheet1:Sheet9!C3:X100. One way around that is specifying opposite corners, so Sheet1!C3 and Sheet9!X100 as separate arguments. However, Excel wouldn't know that the UDF should depend on ALL cells in C3:X100 in all worksheets from Sheet1 to Sheet9. Adding an optional kludge argument as above and calling the UDF with COUNT(Sheet1:Sheet9!C3:X100) as the kludge argument makes the formula dependent on all cells boinded by the opposite corners.

[CK76]

Ah, that's pretty handy trick.

[lsmith999999]

Quote Originally Posted by CK76 View Post
. . .
Meaning... =CountIfCode(C4:AB4, "s", Mar!AC4) will change if C4:AB4 of 'Apr' sheet is changed, but will not update if you change value in Mar!C4:AC4 range.
. . . Application.Volatile . . .
NOTE: In most cases, I'd advise against this practice. . . .

My testing shows that if "s" is changed (without making the function volatile), "CountIfCode()" is called on all sheets from the given sheet on (including the previous sheet strangely, but not including Dec., at least in the example I attached - read on). It occurs in a seemingly random order though, but there's obviously some method to the madness (and I've even seen the order change after adding/deleting 's' multiple times but you wouldn't think these changes should impact the order - the order seems to have no rhyme or reason). I haven't had time to think about why the order is occurring the way it is though since I've been too busy trying different ideas to make it work (such as changing the function's argument order, assigning names to the ranges and using those instead, calculating the ranges on-the-fly using different permutations of both VBA and the Excel UI, etc.). All just grasping at straws to see what impacts the situation. Different changes cause the order to change in different ways but you wouldn't (logically) think so.

At one point I even tried some idea and the order actually worked (consistently) but I couldn't rely on it because there was additional (mandatory) work still not yet complete, which later caused the order to change again (and I still wasn't convinced the order was stable anyway). Clearly the dependencies are kicking in but not in a reliable way I've had time to figure out. Presumably MSFT has thought this through much deeper than I have and Excel is a very mature product so there must (hopefully) be a valid reason. On the surface though the order appears bewildering and chaotic (with or without my programmer's hat on).

At this point the reasons don't matter though. I can't rely on it so will try events.

Thanks for the additional info (hack) you provided. Will review it in more detail a bit later.