How to automatically update the contact when the status is "Submitted"
I have two different sheets. Sheet A is a list of contacts by office/division. Sheet B is a sheet that contains an approval path, based on the office/division. I want to use a formula to automatically pull in the correct contact person into Sheet A from Sheet B, only if the status on the Sheet A is "Submitted". If the status is not "submitted" I do not want the contact person to update, but stay who the original contact was. This is so that we can easily update the contact person on our sheets if someone is out of the office, or if staffing changes. The current formula I am using is below. It is correctly updating when the status is "Submitted", however, I cannot figure out how to prevent it from updating other cells where the status is not "Submitted". I have tried many different formulas with no luck. The image below shows that the contact person is being removed if the status is not "submitted".
=IF([Program Monitor Status]@row = "Submitted", INDEX({Copy of Office/Program Staff Directory Range 3}, MATCH(Program@row, {Copy of Office/Program Staff Directory Range 4}, 0)))
Answers
-
Just to clarify, are you looking for a column to be available for people to manual update & also look for the contact to automatically update when the status is submitted?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 495 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!