How do I align user's role based on a point in time occurrence?
I manage a vendor relationship that includes tracking the usage of their services on a monthly basis. The vendor services are available to multiple internal groups and part of the reporting is tracking the percentage of total services that each group utilized in a given month. On occasion, a user may transfer to a new group (and this may occur multiple times over the course of a year).
I'm trying to develop a method that will:
- Recognize when a user transfer occurs to a new group after a certain date.
- Reflect the user's new group for any usage after the transfer date.
- Continue to identify the prior group for any occurrence before that date.
Each user is identified by a unique ID and when a new month is pasted in, the raw data sheet references another sheet ("name sheet") that stores the user's full name and group they are aligned to. While I can typically use index/match to bring in the details, the challenge is when the user changes roles. I'd like to avoid having to "paste as values" for the prior role and use the index/match after a certain date (there are 50+ unique users and handing off this process to others would get too complex).
Any ideas? Please see a simple screenshot below of how the raw data sheet looks. The column formulas in "User Name" and "User Group" reference the name sheet using index/match. For this example, are there any suggestions on how I can use formulas in the raw data and name sheet to recognize that on 08/17/21 user mfoss transferred to the "Sales" group. Reminder that I would still need the dates prior to 08/17/21 to reflect the old role of "Back Office."
Note: I've considered having other columns on the background sheet to indicate a date change and tie it into existing formulas, but am having trouble coming up with a reliable method that at MOST just requires an administrator to input the date of a change and new group and the formula does the rest.
Thanks in advance! Happy to answer additional questions as this is a little bit on the complex side.
Help Article Resources
Check out the Formula Handbook template!