Forum Discussion
Date Return From Calendar
Hello everyone: new here Thanks in advance
Goal: I need to return the latest date a manager co-travelled with a Sales Rep.
I created a basic calendar, stating in Cell A3 I put all calendar dates running down column A. I then have 13 reps starting in cell B2 - N2 (running across row 2). All of the cells within are drop down menus containing the names of the same 5 managers.
I need to know when the last time a manager co-travelled with a Sales Rep. Knowing that a manager can co-travel with a single rep more then one time through out the year.
Thoughts?
Thanks
Steve
Do you want to know
- One date: the last date any manger co-travelled with any sales rep
- A list of 13 dates, one for each sales rep
- A list of 5 dates, one for each manager
- A 5 by 13 list, one date for each manager/sales rep combination
- SteveSorrilloCopper Contributor
HansVogelaar Thank you for replying to me Hans.
I would like to know the last date each of the 5 managers co-travelled with each of the 13 Sales Reps.
For example manager A co-traveled with Rep 1 on 1/15/24, and Co -Traveled with Rep B on 2/4/24. I would like to return the dates:
I have a separate calendar i created that houses dates and names
Let's say the first sheet is named Data Sheet.
In C3 on the overview sheet:
=LET(m, MAXIFS('Data Sheet'!$A$3:$A$368, INDEX('Data Sheet'!$B$3:$N$368, 0, XMATCH($B3, 'Data Sheet'!$B$2:$N$2)), C$2), IF(m=0, "", m))
Fill down, then to the right (or vice versa)