Default one field value based on item selected in another fields dropdown
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)
Answers
-
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
-
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 = RequiredCategory = WD-Enhancement Tier II (Existing Process), then UAT1 = Required
Category = WD-Break Fix, then UAT1 = Required
Category = WD-Security, then UAT1 = Required -
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
-
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
- How do I make this formula always populate as a new ticket request gets added to the spreadsheet?
- 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
-
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.
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
-
@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
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!