Opportunities matching hygiene moment

Vinton Douglas

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?

opportunities.png

Kelly Moore

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

Kelly Moore

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

https://help.smartsheet.com/articles/522221-collect-information-form

Vinton Douglas

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.

Kelly Moore

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