Manual Override of Formula Index/Match for Out of Office with Column Formula

Kahlan_

SSM override.PNG

SSM formula.PNG

Hi All,

I have an index/match pulling in data relevant to a Business Unit from off sheet. I need to get it so that I can override this formula data to be blank/allow entering of a different email address by the sheet user.

What I have setup already (working with Column formula):

1) User inputs Business Unit (BU)

2) BU auto pulls in via index/match off sheet data for the approver email of that BU

3) I have a second column on sheet, that checks if GTO = Yes and if so, pull different off sheet email. (See attachment) Otherwise it will just reference the cell from step 2) above.

I've tried a number of things already. Was tinkering with a nested If statement inside of (see SSM override.PNG) to blank out the cell if another toggle (say a checkbox or drop down) is used. But I couldn't get it functioning. On sheet automation of trying to clear the cell doesn't work due to column formula being used.

I need to make it so I can override all of that and have a blank cell that someone can type a new email into, preferably on sheet if possible. This would allow the sheet user to type in a new/different email when an approver is out of office, etc. I am struggling with this. Thanks for the help!!

Paul Newcome

You are going to want to put the override argument first.

If the - [Override SSM]@row <> "" - then output - [Override SSM]@row, otherwise run the other formula.

=IF([Override SSM]@row <> "", [Override SSM]@row, original_formula)

Kahlan_

@Paul Newcome Wanted to say thank you!! That was it!

Here's what the finalized version looked like below. Also, it was having issues and saying #value expected or something like that. I had to delete it and manually rebuild it and then it functioned just fine!

=IF([Override SSM]@row <> "", [Override SSM]@row, IF([GTO-11]@row = "Yes", {BU Reference Sheets Range 5}, [Assigned SSM (off sheet Hidden fx)]@row))

This makes it so, highest importance; if override SSM has a value, populate it. Then if GTO-11 is Yes from the drop down, update with GTO approver email from off sheet. Then if GTO-11 is anything else and no override, then bring in a value from another hidden helper column [Assigned SSM (off sheet hidden fx)].

Paul Newcome

You would need to use a different column for users to be able to manually enter a different name. Then your column formula would incorporate this manual entry column to say that if the manual column is not blank then pull that, otherwise run your previous formula.

Kahlan_

@Paul Newcome

Oh okay, that sounds perfect honestly! I've never written anything like this and I need to nest it inside another if statement. So it would be:

This is my existing statement that is working

=IF(([GTO-11]@row) = "Yes", {BU Reference Sheets Range 5}, [Assigned SSM (off sheet fx)]@row)

Unsure of how to add this into the IF statement I already have. Override SSM will be the value I want to overwrite everything else in the column. So it should overwrite GTO-11 also. Any ideas?

=IF((([GTO-11]@row) = "Yes", {BU Reference Sheets Range 5}, [Assigned SSM (off sheet fx)]@row), IF(NOT(ISBLANK([Override SSM]@row))))

Paul Newcome

You are going to want to put the override argument first.

If the - [Override SSM]@row <> "" - then output - [Override SSM]@row, otherwise run the other formula.

=IF([Override SSM]@row <> "", [Override SSM]@row, original_formula)

Kahlan_

@Paul Newcome Wanted to say thank you!! That was it!

Here's what the finalized version looked like below. Also, it was having issues and saying #value expected or something like that. I had to delete it and manually rebuild it and then it functioned just fine!

=IF([Override SSM]@row <> "", [Override SSM]@row, IF([GTO-11]@row = "Yes", {BU Reference Sheets Range 5}, [Assigned SSM (off sheet Hidden fx)]@row))

This makes it so, highest importance; if override SSM has a value, populate it. Then if GTO-11 is Yes from the drop down, update with GTO approver email from off sheet. Then if GTO-11 is anything else and no override, then bring in a value from another hidden helper column [Assigned SSM (off sheet hidden fx)].

Paul Newcome

Happy to help. 👍️