Opportunities matching hygiene moment
I am trying to create a formula to ensure that the opportunities match with the hand hygiene moment (See below). Hoping to capture this info via submitted form. Is there a formulla I can use to ensure that they match?
Best Answer
-
Perfect!
We have a few ways we can build a solution. Do you have more possible responses or is the above all you have? Or are you planning on adding more in the future?
I ask because the solution that is the easiest solution to update and maintain in the future is to build a lookup table in a separate sheet and pull the answers from that. The lookup table would look exactly li(or two helper columns) like the screenshot above. This approach would give you the greatest freedom in adding, editing, or deleting the information. We would use an INDEX/MATCH with this approach.
In case you don't want that approach, I'll give you the nested IF below.
=IF([What Hand Hygiene Moment Are You Evaluating?]@row<>"", IF(OR([What Hand Hygiene Moment Are You Evaluating?]@row= "Before Entering & Leaving A Pt. Room/Cubicle", [What Hand Hygiene Moment Are You Evaluating?]@row="Before & After Touching a Patient", [What Hand Hygiene Moment Are You Evaluating?]@row="Before & After Clean/Aseptic Procedure"), 2, 1),"")
If you want the lookup approach, build the lookup sheet (or dedicate two helper columns in the current sheet as lookup columns) and let me know. I'll help you with the Index/Match if you need help
cheers
Kelly
Answers
-
Hey @Vinton Douglas
Help me better understand what you are trying to accomplish. Are you saying that anytime someone selects 'Before & After Touching a Patient' they need to enter a 2 for Opportunities?
If this is what you asking, first I would recommend not having the number selection as part of the form. It would put an unnecessary burden on the user to select something that is completely determined by a previous response. And yes, we can have a formula in the sheet to determine this. I can help you with that.
Formulas, however, cannot be used within a form. Depending upon how your sheet and columns are set up, there may be other options that are possible within the form. I will wait for your clarification above before advising further.
Kelly
-
Hi Kelly,
Thanks much for responding.Thats correct. The opportunities can either be 2 or 1 as seen in the attachment. These "opportunities" are predetermined based on hand hygiene moment. And yes, I dont want the opportunities to be selected by the user. I hope that based on the moment selected, the opportunity will automatically be selected without the user doing so. Any help would be grateful.
-
Perfect!
We have a few ways we can build a solution. Do you have more possible responses or is the above all you have? Or are you planning on adding more in the future?
I ask because the solution that is the easiest solution to update and maintain in the future is to build a lookup table in a separate sheet and pull the answers from that. The lookup table would look exactly li(or two helper columns) like the screenshot above. This approach would give you the greatest freedom in adding, editing, or deleting the information. We would use an INDEX/MATCH with this approach.
In case you don't want that approach, I'll give you the nested IF below.
=IF([What Hand Hygiene Moment Are You Evaluating?]@row<>"", IF(OR([What Hand Hygiene Moment Are You Evaluating?]@row= "Before Entering & Leaving A Pt. Room/Cubicle", [What Hand Hygiene Moment Are You Evaluating?]@row="Before & After Touching a Patient", [What Hand Hygiene Moment Are You Evaluating?]@row="Before & After Clean/Aseptic Procedure"), 2, 1),"")
If you want the lookup approach, build the lookup sheet (or dedicate two helper columns in the current sheet as lookup columns) and let me know. I'll help you with the Index/Match if you need help
cheers
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!