Data Validation with offset, Match and count if

Charlene Durand

Board Regular
Joined
Sep 19, 2015
Messages
94
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
1598530248994.png



TIMELOG TAB

1598530324393.png


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)

1598530183949.png


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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,942
Office Version
  1. 365
Platform
  1. Windows
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.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,942
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,973
Members
414,115
Latest member
SFUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top