Data Validation with offset, Match and count if

[Charlene Durand]

I have a excel time tracking. in this it has a projects tab this is where my dropdowns filter from when working on the timeLog tab

PROJECTS TAB

TIMELOG TAB

The formula in the dropdown for Service is in a data validation and this is the formula =OFFSET(taskIDlabel,MATCH(Client Name,taskIDList_ProjectID,0),0,COUNTIF(taskIDList_ProjectID,Client Name),1)

I am now trying to get the task drop down to look at the client name then the service, then give me the list of only what is for that.

is this possible

 

[jasonb75]

With screen captures and named ranges / tables it is going to be more guesswork that accurate suggestion. I've made some changes and marked them in bold, you will need to correct these to match the actual names / ranges in your data.

=OFFSET(taskIDlabel,AGGREGATE(15,6,ROW(taskIDList_ProjectID)/(Client Name=taskIDList_ProjectID)/(taskIDList_Project=Service),1)-MIN(ROW(taskIDList_ProjectID)),0,COUNTIFS(taskIDList_ProjectID,Client Name,taskIDList_Project,Service),1)

Data validation doesn't usually accept arrays, so you might have to enter the underscored part into a cell or named range definition (as a helper) then refer to the result of that in the validation formula.

 

[Charlene Durand]

Thank you what is the AGGREGATE(15,6,ROW suppose to look at

 

[jasonb75]

Sorry, I shouldn't have had that part in bold, some of the ranges in the aggregate function directly replace your original match function.

=OFFSET(taskIDlabel,AGGREGATE(15,6,ROW(taskIDList_ProjectID)/(Client Name=taskIDList_ProjectID)/(taskIDList_Project=Service),1)-MIN(ROW(taskIDList_ProjectID)),0,COUNTIFS(taskIDList_ProjectID,Client Name,taskIDList_Project,Service),1)

The section of ROW(range)/(range1=criteria1)/(range2=criteria2) creates an array of row numbers that meet the criteria, AGGREGATE(15,6 grabs the first matching row from the array.
MIN(ROW(range)) then adjusts that so that the first row (matching or not) in the array always equates to zero.