Default one field value based on item selected in another fields dropdown

k_Hunter001

Hello, what I am trying to accomplish is in a PMO tracker we have a field that has a list of drop down items based on the type of Project Request IE New, Enhancement, Breakfix and need an associated field to default a value to support resources processes. (sorry very new to Smartsheet)

Kelly Moore

Hey @k_Hunter001

I think you are asking for an IF statement to populate your UAT1 column?

For instance

IF(Category@row<>"", IF(OR(Category@row="WD-Enhancement Tier 1 (Existing Process)", Category@row="WD-New Function"),"Required", "(Not Required)"))

If you can write out the responses you want in plain text, I can help you with the formula.

For example, in plain text: when category = WD-Enhancement Tier 1 (Existing Process), then UAT1 = Required.

@mention me if you respond.

Will that work for you?

Kelly

k_Hunter001

Hi Kelly, thank you for your assistance on this request it will be a huge help in ensuring our teams follow required practices.

Category = WD-New Function, then UAT1 = Required
Category = WD-Enhancement Tier I (Existing Process), then UAT1 = Required

Category = WD-Enhancement Tier II (Existing Process), then UAT1 = Required
Category = WD-Break Fix, then UAT1 = Required
Category = WD-Security, then UAT1 = Required

k_Hunter001

@Kelly Moore

Kelly Moore

Hey @k_Hunter001

If these criteria given above are not met, then is the response "(Not Required)"? The screenshot above is truncated so I cannot see the full response. Is there ever a time when the cell should be blank? When building nested IF statements, these are the questions to ask yourself.

The formula below will return a "Required" for the specific responses below, otherwise it returns a (Not Required). If the category field is blank then the UAT1 field is blank.

=IF(Category@row<>"",IF(OR(Category@row = "WD-New Function", Category@row = "WD-Enhancement Tier I (Existing Process)", Category@row = "WD-Enhancement Tier II (Existing Process)", Category@row = "WD-Break Fix", Category@row = "WD-Security"), "Required", "(Not Required)"))

Will this work for you? Let me know if you need the formula tweaked for other criteria. Be sure to @mention me so I can find the email in my inbox. 😉

Kelly

k_Hunter001

@Kelly Moore

This is so helpful and when I manually add it to the field it is population exactly right as we will not want a blank it is always one or the other.

If I could impose I have two asks

1. How do I make this formula always populate as a new ticket request gets added to the spreadsheet?
2. We have a second field UAT 2 that is a bit different rules

Category = WD-New Function, then UAT2 = Required

Category = WD-Enhancement Tier I (Existing Process), then UAT2 = Required

Category = WD-Enhancement Tier II (Existing Process), then UAT2 = Required

Category = WD-Break Fix, then UAT2 = Not Required

Category = WD-Security, then UAT2 = Not Required

Any other options UAT2 = Not Required

Kelly Moore

Hey @k_Hunter001

To make the formula always populate new rows you will want to turn the formula into a column formula. You do this by right clicking INTO the cell where the correct formula resides, and selecting Convert to Column Formula at the bottom of the cell menu. It is important to know you will have to right click into the cell and select Edit Column Formula to make any changes to the formula after a formula has been converted. The reference below gives instruction about column formulas.

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas#:~:text=To%20create%20a%20column%20formula,%2C%20and%20cross%2Dsheet%20references.

For your UAT2 column formula:

=IF(Category@row<>"",IF(OR(Category@row = "WD-New Function", Category@row = "WD-Enhancement Tier II (Existing Process)"),"Required", "Not Required"))

Let me know if this doesn't work for you

Kelly

k_Hunter001

@Kelly Moore could I impose on one more formula ask so I can see how an equal would appear. This would complete my workbook and allow us to deploy this to general use.

When we have the Team/Dept come in form the request for mor if we update it then the Sponsor-Approve should auto populate. Given these are VP/Above there is low movement so it wont take much maintenance but it would make it easier on our PM team especially when we add new members who don't know the Org as well.

Accounting & Finance = Maria Salamante

Talent Acquisition = Brandon Carmack

Onboarding = Brandon Carmack

HCM = Justin Thomas

Time & Absence = Justin Thomas

Advance Comp(Comp/Bonus/Merit/Equity/) = Chelsea Hernandez

Performance = Chelsea Hernandez

Benefits = Kamille Jimenez

Payroll = Kamille Jimenez

License, Credential, Vaccination = Les Polk

EWF = Brandon Carmack

Kelly Moore

Hey @k_Hunter001

I'm sorry for the delay. I took advantage of the US holiday week.

Your Sponsor-Approver field must be a contact field if you intend to use this field to send notifications. If you do intend to use the field for notifications, you will need to swap the Approver names for their email addresses. You will keep the quote marks but replace the entire name with the complete email address. It would look like this inside the formula "jane.doe@somedomain.com"

=IF([Team/Department]@row <> "", IF([Team/Department]@row = "Accounting & Finance", "Maria Salamante", IF(OR([Team/Department]@row = "Talent Acquisition", [Team/Department]@row = "Onboarding", [Team/Department]@row = "EWF"), "Brandon Carmack", IF(OR([Team/Department]@row = "HCM", [Team/Department]@row = "Time & Absence"), "Justin Thomas", IF(OR([Team/Department]@row = "Advance Comp(Comp/Bonus/Merit/Equity/)", [Team/Department]@row = "Performance"), "Chelsea Hernandez", IF(OR([Team/Department]@row = "Benefits", [Team/Department]@row = "Payroll"), "Kamille Jimenez", IF([Team/Department]@row = "License, Credential, Vaccination", "Les Polk")))))))

Will this work for you?

Kelly

PS: I decided to give you the solution as a nested IF since you didn't have that many names. Alternatively, amore robust solution would have used a separate sheet for the names and departments, and a straight forward Index/Match for the formula. The separate sheet is easier to update than editing the actual formula. If you are interested in this approach, build a separate sheet that has the Team/Department field and the department names as an Exact match to how they will appear on this sheet. Add a contact field and select the appropriate person for each row. I can help you with the Index/Match and cross sheet reference once you get that sheet built.