Referencing value in previous row to assign a contact
On a Requests Sheet, I have a column for Assigned Supervisor. I want to write a column formula that will add a contact based on the previous row's assignment. I'd like to rotate through a list of supervisor contacts each time a form is submitted.
I created a separate Assignment sheet that has a column of the Supervisor and a column Next Assigned Supervisor. I'm trying to write the formula using INDEX and MATCH in the Assigned Supervisor columns of the Requests Sheet that will match the value of the previous row's Assigned Supervisor tp the Assignment sheet's Supervisor Column and return the Next Assigned Supervisor value.
The two areas I am struggling with are:
- How do I reference the previous row in the column formula?
- How do I account for the first row in the column formula?
I'm also open to other approaches.
Help Article Resources
Check out the Formula Handbook template!