Forum Discussion
Stopping a MAX function after one result.
Your Excel version doesn't support MAXIFS().
Use AGGREGATE() instead.
=IF(COUNTIFS(ActDName,A3,ActionDevelopment!$F$2:$F$14,"Yes"),"",AGGREGATE(14,6,ActionDevelopment!$D$2:$D$14/(ActDName=A3),1))
However, where there are multiple entries, but none are resolved i.e. the plan is still active, it's returning a #NAME? error, not the latest date?
Your Excel version doesn't support MAXIFS().
Use AGGREGATE() instead.
=IF(COUNTIFS(ActDName,A3,ActionDevelopment!$F$2:$F$14,"Yes"),"",AGGREGATE(14,6,ActionDevelopment!$D$2:$D$14/(ActDName=A3),1))
- I think I've sorted it. Thank you for all your help Detlef Lewin, it's very much appreciated.
Still a problem. It works, but doesn't match against the names e.g. it is bringing back the values from the ActionDevelopment sheet but not against the right name? It just brings back the dates I've entered (where I've entered 5 dates) against the top 5 people not the actual person they've been applied against. If I enter dates for the top 5 people, it works, but if I enter the top person, then 2 from the middle and 2 from the bottom, it lists those 5 dates against the top 5 people, not the correct names.
Could I ask what the 14 and 6 mean in the Aggregate formula, as Aggregate is not a formula I've used before.
NB. I can't send you my actual sheet as it has some customer data in it.
Helper column S:
=AGGREGATE(14,6,ActDDate/(ActDName=ActionDevelopment!A2),1)
Helper column T:
=IFERROR(AGGREGATE(14,6,ActDDate/(ActDName=ActionDevelopment!A2)/(ActDRes="Yes"),1),0)
Formula in column R:
=IF(S3>T3,S3,IF(S3=T3,"","…"))
Updated sample file attached.
If you look at the ActionDevelopment tab, User 1 and User 2 have had their 2017 plans resolved ('yes' in column E) but a later date has now been added in 2018 i.e. another development plan has been put in place. However, the main sheet is showing blanks, instead of 26/07/2018 and 15/08/2018 respectively.
Please provide an updated sample file.
- Sorry, no it doesn't work completely. ;o(
It does prevent it bouncing back to a previous date when a plan is satisfied and the result turned to yes and returns a blank instead, which is brilliant, and exactly what I want it to do but if a new plan is then added with a later date, it's still bringing back a blank, instead of the new date? - Detlef Lewin you are an absolute Legend.
That works perfectly.
Thank you so much. ;o))
