If Statement for Contact in another list and a flag is raised
Hello Again,
I am trying to have a value populate (Which is a contact) if another contact is part of a list and a flag is raised in that same row.
Some background if that wasn't clear, I have two sheets, one where employees submit if they are out of office, it has their name and the person to cover, as well as the date they will be out and another flag column that raises if today is within the date range signalling they are currently OOO.
On a separate sheet issue come in via form for each employee, I want to create a formula that says if a new submission comes in with an employee that is currently out of office (based on the other sheet), then fill in a cell with the contact that is covering while they are out.
I thought I was on to something but keep getting errors, another wrinkle is in the Out of Office sheet there may be the same employee name from previous submissions but only one will be current with a flag raised.
=IF(AND(MATCH([Account Manager Name]1, {Out of Office Range 2}), {Out of Office Range 3} = 1), INDEX({Out of Office Range 5}, MATCH([Account Manager Name]1, {Out of Office Range 2}, 0)), 0)
Thanks for the help
Best Answer
-
First you would want to pull the value of the checkbox as to whether or not they are out.
=INDEX({Other Sheet Checkbox Column}, MATCH([Account Manager Name]@row, {Other Sheet Name Column}, 0))
Then you say that if that box is checked
=IF(above = 1,
=IF(INDEX({Other Sheet Checkbox Column}, MATCH([Account Manager Name]@row, {Other Sheet Name Column}, 0)) = 1,
then you want to pull the associated name from the back-up column
INDEX(COLLECT({Other Sheet Back-Up Column}, {Other Sheet Name Column}, [Account Manager Name]@row, {Other Sheet Checkbox Column}, 1), 1)
and drop that in as the output for the IF statement.
=IF(INDEX({Other Sheet Checkbox Column}, MATCH([Account Manager Name]@row, {Other Sheet Name Column}, 0)) = 1, INDEX(COLLECT({Other Sheet Back-Up Column}, {Other Sheet Name Column}, [Account Manager Name]@row, {Other Sheet Checkbox Column}, 1), 1))
Answers
-
First you would want to pull the value of the checkbox as to whether or not they are out.
=INDEX({Other Sheet Checkbox Column}, MATCH([Account Manager Name]@row, {Other Sheet Name Column}, 0))
Then you say that if that box is checked
=IF(above = 1,
=IF(INDEX({Other Sheet Checkbox Column}, MATCH([Account Manager Name]@row, {Other Sheet Name Column}, 0)) = 1,
then you want to pull the associated name from the back-up column
INDEX(COLLECT({Other Sheet Back-Up Column}, {Other Sheet Name Column}, [Account Manager Name]@row, {Other Sheet Checkbox Column}, 1), 1)
and drop that in as the output for the IF statement.
=IF(INDEX({Other Sheet Checkbox Column}, MATCH([Account Manager Name]@row, {Other Sheet Name Column}, 0)) = 1, INDEX(COLLECT({Other Sheet Back-Up Column}, {Other Sheet Name Column}, [Account Manager Name]@row, {Other Sheet Checkbox Column}, 1), 1))
-
Thank you for your response, I see how that could work. I ended up making a new column to create a single value to search for in the Person was in the office or not, =[Account Manager]+[Out of Office], this gave me a single value JoeBlowtrue or JoeBlowfalse. So then I did a Vlookup to search for [Account Manager]@cell + true and that is returning the Out of Office contact only if the person is out of office.
Thanks again for the help.
-
Happy to help! 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 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!