Pre-Populating A Dropdown Field In Smartsheet Form Based on Previous Dropdown Selections

Benjamin O'Leary

Hello,

I am currently working on a form with seven dropdown fields. Each field has 2-3 dropdown options. I also have a dropdown field with two options (Approved and Notification) that I need to be automated. I need to set the form up so when a user selects certain options from the seven dropdown fields, the Approved/Notification dropdown field automatically populated to Approved, whereas other options from the seven dropdown fields will automatically populate the Approved/Notification field to Notification.

I've seen some formulas online that might be able to help, though I'm unsure due to the large number of dropdown fields/options the user can choose from. Is there a way to perform this task? I can attempt to go into greater detail if necessary.

Thank You,

Ben

Paul Newcome

How many total options do you have and what is the breakdown for Approved vs Notification?

Benjamin O'Leary

@Paul Newcome

I have a 'Yes/No' dropdown where 'Yes' will lead to Approved being pre-populated.

I have a 'GC' dropdown with option 1 will lead to Approved being pre-populated. and option 2 will lead to Notification being pre-populated.

I have an 'L' dropdown where option 1 will lead to Approved being pre-populated, and options 2 and 3 will lead to Notification being pre-populated.

I have an 'M' dropdown where option 1 will lead to Approved being pre-populated, and option 2 will lead to Notification being pre-populated.

I have an 'RP' dropdown where options 1 and 4 will lead to Approved being pre-populated, and options 2, 3, 5 will lead to Notification being pre-populated.

I have an 'SW' dropdown where option 1 will lead to Approved being pre-populated, and option 2 will lead to Notification being pre-populated.

Paul Newcome

It looks like you have less options that prepopulate Approved than those that prepopulate Notification, so lets work from there. We can just use a basic IF/OR for this one.

All of the below would prepopulate "Approved". If it isn't one of the below then we are populating "Notification".

[Yes/No]@row = "Yes"

GC@row = "Option 1"

L@row = "Option 1"

M@row = "Option 1"

RP@row = "Option 1"

RP@row = "Option 4"

SW@row = "Option 1"

=IF(OR([Yes/No]@row = "Yes", GC@row = "Option 1", L@row = "Option 1", M@row = "Option 1", RP@row = "Option 1", RP@row = "Option 4", SW@row = "Option 1"), "Approved", "Notification")

Benjamin O'Leary

@Paul Newcome I am using the following formula to auto-populate the 'Approved/Notification' column:

=IF(OR([LM Approval Req. By The Affiliate's Local Author?]@row = "Yes", CONTAINS("L1: Adding labels to finished products that modify, add, or cover quality or global regulatory relevant information", [What is In Scope of Your Local Modification?]@row), CONTAINS("M1: Modifying of products by adding, removing, or replacing components such as finished products or printed materials", [What is In Scope of Your Local Modification?]@row), CONTAINS("RP1: Re-packaging - bundling of products - Making available bundles of different IVD and/or MD products, e.g. a kit with a MD and an IVD", [What is In Scope of Your Local Modification?]@row), CONTAINS("RP4: Re-packaging: Splitting/dismantling of original pack sizes/opening any quality controlled packaging; products in large packs are split into smaller units or single items of product with new labelling (Info button content)", [What is In Scope of Your Local Modification?]@row), CONTAINS("SW1: SW modifications: Reconfiguring SW with other than the parameters suggested by the instructions provided by the legal ", [What is In Scope of Your Local Modification?]@row), CONTAINS("GC1: A global change has been rolled out that affects any currently valid local variant (e.g. local IFU), however as an Affiliate you want to remain with your current variant and do not wish to adopt the change", [What is In Scope of Your Local Modification?]@row)), "Approval", "Notification")

The formula works, but for some reason when L1 is selected, it auto-populates to 'Notification' instead of 'Approval'. I don't know what I need to adjust in order to resolve this.

Paul Newcome

@Benjamin O'Leary Double check that the text in the dropdown selection is an exact match (including spaces) to what you have in the formula. Copy/paste is a good way to make sure there are no errant spaces or letters swapped around when searching for longer text strings like that.

Benjamin O'Leary

@Paul Newcome I'm not sure what you mean. The formula works, I just need to switch one aspect so that when L1 is selected 'Approval' appears.

Paul Newcome

Right. So the reason it is populating "Notification" is because when you make that selection in the sheet, the formula is not recognizing it. This means that what you have in your dropdown and what you have in your formula do not match exactly which is why it is outputting the "value if false" piece (because there are none triggering it as true).

Benjamin O'Leary

@Paul Newcome I modified the formula:

=IF(OR([LM Approval Req. By The Affiliate's Local Author?]@row = "Yes", CONTAINS("L1: Adding labeling to finished products that modify, add, or cover any quality or regulatory relevant information provided by the Legal Manufacturer and/ or which requires opening QCed packaging", [What is In Scope of Your Local Modification?]@row), CONTAINS("M1: Modifying of products by adding, removing, or replacing components such as finished products or printed materials", [What is In Scope of Your Local Modification?]@row), CONTAINS("RP1: Re-packaging - bundling of products - Making available bundles of different IVD and/or MD products, e.g. a kit with a MD and an IVD", [What is In Scope of Your Local Modification?]@row), CONTAINS("RP4: Re-packaging: Splitting/dismantling of original pack sizes/opening any quality controlled packaging; products in large packs are split into smaller units or single items of product with new labelling (Info button content)", [What is In Scope of Your Local Modification?]@row), CONTAINS("SW1: SW modifications: Reconfiguring SW with other than the parameters suggested by the instructions provided by the legal manufacturer, adding or removing part of the SW files, creating helper software to interact with the Roche Software", [What is In Scope of Your Local Modification?]@row), CONTAINS("GC1: A global change has been rolled out that affects any currently valid local variant (e.g. local IFU), however as an Affiliate you want to remain with your current variant and do not wish to adopt the change", [What is In Scope of Your Local Modification?]@row)), "Approval", "Notification")

The text in the dropdown and the text in the formula match, and I'm still having the same issue (when L1 is selected, 'Notification' pops up instead of 'Approval'.

Paul Newcome

How about this... Do you have multiple options that start with "L1" (or any of the other prefixes in your formula)? If not, you can separate the prefix to just grab everything before the column and compare it to that.

=IF(OR([LM Approval Req. By The Affiliate's Local Author?]@row = "Yes", LEFT([What is In Scope of Your Local Modification?]@row, FIND(":", [What is In Scope of Your Local Modification?]@row) - 1) = "L1", LEFT([What is In Scope of Your Local Modification?]@row, FIND(":", [What is In Scope of Your Local Modification?]@row) - 1) = "M1", LEFT([What is In Scope of Your Local Modification?]@row, FIND(":", [What is In Scope of Your Local Modification?]@row) - 1) = "RP1", .............................

Benjamin O'Leary

@Paul Newcome I used that method and got an INCORRECT ARGUMENT error:

=IF(OR([LM Approval Req. By The Affiliate's Local Author?]@row = "Yes", LEFT([What is In Scope of Your Local Modification?]@row, FIND(":", [What is In Scope of Your Local Modification?]@row) - 1) = "GC1", LEFT([What is In Scope of Your Local Modification?]@row, FIND(":", [What is In Scope of Your Local Modification?]@row) - 1) = "L1", LEFT([What is In Scope of Your Local Modification?]@row, FIND(":", [What is In Scope of Your Local Modification?]@row) - 1) = "M1", LEFT([What is In Scope of Your Local Modification?]@row, FIND(":", [What is In Scope of Your Local Modification?]@row) - 1) = "RP1", LEFT([What is In Scope of Your Local Modification?]@row, FIND(":", [What is In Scope of Your Local Modification?]@row) - 1) = "SW1", LEFT([What is In Scope of Your Local Modification?]@row, FIND(":", [What is In Scope of Your Local Modification?]@row) - 1) = "GC2", LEFT([What is In Scope of Your Local Modification?]@row, FIND(":", [What is In Scope of Your Local Modification?]@row) - 1) = "L2", LEFT([What is In Scope of Your Local Modification?]@row, FIND(":", [What is In Scope of Your Local Modification?]@row) - 1) = "M2", LEFT([What is In Scope of Your Local Modification?]@row, FIND(":", [What is In Scope of Your Local Modification?]@row) - 1) = "RP2", LEFT([What is In Scope of Your Local Modification?]@row, FIND(":", [What is In Scope of Your Local Modification?]@row) - 1) = "SW2", LEFT([What is In Scope of Your Local Modification?]@row, FIND(":", [What is In Scope of Your Local Modification?]@row) - 1) = "L3", LEFT([What is In Scope of Your Local Modification?]@row, FIND(":", [What is In Scope of Your Local Modification?]@row) - 1) = "RP3", LEFT([What is In Scope of Your Local Modification?]@row, FIND(":", [What is In Scope of Your Local Modification?]@row) - 1) = "RP4", LEFT([What is In Scope of Your Local Modification?]@row, FIND(":", [What is In Scope of Your Local Modification?]@row) - 1) = "RP5", "Approval", "Notification"))

Paul Newcome

You forgot to close out your OR statement. Move one of the closing parenthesis from the very end of the formula to immediately after "RP5".

Benjamin O'Leary

@Paul Newcome I'm still getting the same issue where when a user selects L1 it marks it as an 'Approval' as a 'Notification'. I don't know what I'm doing wrong.

Paul Newcome

Can you provide a screenshot of the formula open in the sheet as if you are about to edit it?

Benjamin O'Leary

@Paul Newcome We decided to make a workflow that changes the fields in that column based on different options. I appreciate you assisting me with this.

Paul Newcome

Glad you were able to get it sorted.👍️