Manual Override of Formula Index/Match for Out of Office with Column Formula
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!!
Best Answers
-
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)
-
@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)].
Answers
-
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.
-
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))))
-
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)
-
@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)].
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!