I have an excel table with subtotals.

Some of the subtotals are not updating correctly. Others are!!

In the attached file:
Column [NetAmt2] is dependent on cell C5...named BKSTransUseDate.
Cell M209 holds the subtotal for column [NetAmt2]. It is not updating correctly when cell C5 is changed.

Column [Sh Obl2] is also dependent on cell C5... but it seems to be updating correctly.

I've built a data table in cells $I$216:$O$227 that is dependent on this subtotal being updated correctly when C5 changes.

I know this isn't the cleanest example...but any help would be greatly appreciated.

"DVEINTIMILLA" wrote:
Column [NetAmt2] is dependent on cell C5...named BKSTransUseDate.
Cell M209 holds the subtotal for column [NetAmt2]. It is not
updating correctly when cell C5 is changed.
Column [Sh Obl2] is also dependent on cell C5... but it seems
to be updating correctly.
[....]
Download: http://www.excelbanter.com/attachmen...tachmentid=712

Both M209 and N209 change when I change C5 from 12/20/12 to 6/1/12
arbitrarily. (I am making no effort to try to understand the logic of your
worksheet.)

Note that M209 has the formula =SUBTOTAL(109,[NetAmt2]) and N209 has the
formula =SUM([Sh Obl2]).

My guess is: __you__ change C5 (you neglect to give an example the
demonstrates how), it causes changes in some hidden cells in [NetAmt2], but
no non-hidden cells. Ergo, SUBTOTAL(109,...) correctly remains unchanged.
In contrast, I stumbled onto a value for C5 that changes non-hidden cells
(et al?). Ergo, SUBTOTAL(109,...) does change.

But when I change C5 to 6/1/12, I notice that the amounts in J109 and K109
do not change. They have the formulas =SUBTOTAL(109,[NetAmt]) and
=SUBTOTAL(109,[Sh Obl]).

Is that what you expect? Or are those the unchanging values that you really
want to discuss?

I'm glad that you were able to get Cell M209 to change. It does not update correctly for me.

All the cells in column M are dependent on C5. I am filtering 'out' all entries made after the cell in C5. You are right that not all value changes in C5 would result in a change in M209. But I'm never seeing an update. If I select C5 and put the insertion point in the formula and hit enter (that is force excel to consider that cell dirty)... it does update correctly.

What is truly bizarre is that this only happens when I add that column programatically... that is via VBA code. When I add the column by hand... exact same logic the subtotal does update correctly.

What version of excel are you checking this again. I suspect I may have stumbled across a weird bug in excel tables...but need to test weather this works correctly in other versions (or possibly in the same version but on a different install).

Thanks for the help! Sorry you are not able to repro scenario on your install.

I was able to find a workaround. In the code creates the [NetAmt2] Column and adds the subtotal... I force excel to use relative cell references instead of using the structured table reference.

So the code changes the formula in M209 ....
from : "=SUBTOTAL(109,[NetAmt(2)])"
to : "=SUBTOTAL(109,R[-201]C:R[-1]C)"

This is a little kludgier, but it does the trick.