count unique values in columns based on date values

This struck me as an interesting challenge....

First, I would make a fairly fundamental point... given the complexity of the model and the calculations you should really be looking to avoid Volatility as the performance impact will be significant.

For ex. on 7.6 sheet you are (understandably) using:

Please Login or Register  to view this content.

in cell B16... however this means that every other cell/calculation that utilises this value (via today range) is also Volatile thereby increasing the Calculation overhead in your model.

To avoid this I would suggest you use the VBA workbook open event to update this date value with a static value thereby removing all of this Volatility, eg:

Please Login or Register  to view this content.

(above would reside in ThisWorkbook).

The next point I would make concerns your named ranges - which are presently inconsistent in terms of:

a) their scope (workbook / worksheet)

b) their dimensions (column references)

For the purposes of this exercise alone I would probably advise the following names:

Please Login or Register  to view this content.

You can then in theory use a UDF to do what you want - which given the Volatility is now removed should not perform too badly

Please Login or Register  to view this content.

The above would be called from a cell along the lines of:

Please Login or Register  to view this content.

If you wanted to you could modify the above to return both count and the names included in the count

The above calculation is returning the count of unique names that are active on any given day - not the count of names.

It is assumed that if the project start date prececeds the date criteria and that the "left" value is something other than "Closed" then the project can be deemed to be active.

I hope that helps - and I'm sure it can be improved upon.

To reiterate though - if you implement the above without first removing the volatility your file will perform very badly in Auto Calc mode.

The logic seems sound enough to me - apologies for the oversight on my part regards the perpetual project issue.

I would be wary of using the UDF en masse simply because it isn't a particularly light weight function given use of Dictionary Object.

You should be aware that whenever any of the precedents are altered - ie:

Please Login or Register  to view this content.

every single cell utilising the UDF will be flagged as requiring recalculation.

It follows that on Auto Calc the calculation time will be significant should you have lots of calls to the UDF.

You will notice this lag when opening the file -- given the "today" cell is updated this in turn affects the "end" range and therefore flags the UDF's as requiring a recalc.
The above is both expected & correct (the UDFs do require recalculation as and when that value changes), however, it is just something to be aware of.

If you find you want to leave the file on Auto Calc but wish to control as and when the UDFs all recalculate you can add a pre-emptive IF to each UDF call based on say a "flag".

Using your sample file - let's assume:

-- UDFs in Col B have been removed (legacy UDF)
-- E1 on Summary sheet contains either 0 or 1 where 1 means UDFs should calculate and 0 means they should not.

We can modify the in-cell call to:

Please Login or Register  to view this content.

We will now find that if we set E1 to 0 and subsequently modify the precedent ranges the UDFs do not recalculate even if we're running Excel in Auto Calculation mode.

If we then change E1 to 1 from 0 then the UDFs will all recalculate.

Not an ideal setup but pending the frequency with which you modify the precedent ranges (I'm thinking specifically of enrollers3) you might find it's worthwhile implementing.
In reality the viability of the above all rather depends on the workflow down the line and how critical these numbers are in the overall operation of the model.