How to automatically update the contact when the status is "Submitted"

Paula Reynolds

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)))

Melissa Yamada

@Paula Reynolds if that's the case a formula won't be the best option since using the formula won't save any historical value of a cell. This will update every time your reference sheet updates.

A possible workaround is:

1. create a unique identifier column (can be an auto-number column)
2. create a historical sheet (helper sheet)
3. create a copy row automation that will copy rows every time status changes to "Approved/Denied"
4. create an IF statement on your contact column like:
   1. =IF(Status@row="Submitted",INDEX({Copy of Office/Program Staff Directory Range 3}, MATCH(Program@row, {Copy of Office/Program Staff Directory Range 4}, 0)), INDEX({Contact Column on your helper sheet},MATCH([Unique Identifier]@row,{Unique Identifier Column},0))

NickStaffordPM

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?

Paula Reynolds

Yes, we already have the column that is manually updated on a different sheet - it is our staff directory sheet that we update when staff changes. The sheet that I need the formula on should reflect the correct staff person anytime the status is "submitted". Otherwise, the staff person's name should not change (if it is already approved/denied).

Melissa Yamada

Hello @Paula Reynolds

If what you're trying to do is return the correct contact based on the Program (since contact will be consistent from submitted to approved/ denied) you might want to use INDEX/MATCH or INDEX/COLLECT and remove your IF statement.

=INDEX({Copy of Office/Program Staff Directory Range 3}, MATCH(Program@row, {Copy of Office/Program Staff Directory Range 4}, 0))

This will update your contact column to the correct program staff from submitted to approved/denied.

If there is another criteria (for example, if program monitor status is blank then contact column should be blank) you can just add logical expression on your current formula:

=IF(OR([Program Monitor Status]@row = "Submitted",[Program Monitor Status]@row = "Approved",[Program Monitor Status]@row = "Denied"), INDEX({Copy of Office/Program Staff Directory Range 3}, MATCH(Program@row, {Copy of Office/Program Staff Directory Range 4}, 0)))

Paula Reynolds

Thanks, Melissa, but that does not give me what I need. The issue is, it keeps updating the contact for items already approved/denied. I only want the contact person to update for items that are currently pending, or in my case the status is "Submitted." I do not want it to change anything in the past that has already been approved or denied.

Melissa Yamada

@Paula Reynolds if that's the case a formula won't be the best option since using the formula won't save any historical value of a cell. This will update every time your reference sheet updates.

A possible workaround is:

1. create a unique identifier column (can be an auto-number column)
2. create a historical sheet (helper sheet)
3. create a copy row automation that will copy rows every time status changes to "Approved/Denied"
4. create an IF statement on your contact column like:
   1. =IF(Status@row="Submitted",INDEX({Copy of Office/Program Staff Directory Range 3}, MATCH(Program@row, {Copy of Office/Program Staff Directory Range 4}, 0)), INDEX({Contact Column on your helper sheet},MATCH([Unique Identifier]@row,{Unique Identifier Column},0))

Paula Reynolds

Thank you, Melissa!