Special formula help
Hi,
I'm hoping smartsheet community can help me with an automatic formula.
We have a column called "Priority Deal" this is a symbol selection of the stars, where we rate a specific land opportunity (empty/one/two/three/four/five)
I'd like to create a formula when a specific star is selected, the column "Deadline Date" is automatically populated with a date based on the below critera,
So for example :
Empty = No date
one Star = Five weeks from current date
two stars = Four weeks from current date
Three stars = Three weeks from current date
Four stars = Two weeks from current date
Five starts = One week from current date
Is this possible? Extra columns can be added if it means we can get his working.
I look forward to responses.
Kind Regards
Stuart
Comments
-
Hi Stuart,
Try this.
Add two new columns, Deal Date and Days.
The Deal Date is the date that you want to count the deadline from. The today formula will update to the present day so that won't work.
Will it work that you don't put a date if it's no stars and no deadline?
The formula in the Days column (you can hide this when done) Right now it's just the number of days but you could change it to reflect workdays or something else if needed:
=IF([Priority Deal]@row = "Empty"; ""; IF([Priority Deal]@row = "One"; 35; IF([Priority Deal]@row = "Two"; 28; IF([Priority Deal]@row = "Three"; 21; IF([Priority Deal]@row = "Four"; 14; IF([Priority Deal]@row = "Five"; 7))))))
Formula in the Deadline Date column:
=[Deal Date]@row + Days@row
Please see the attached link/screenshot for more information.
Depending on your country you’ll need to exchange the semi-colon to a comma in the above formula.
Would that work for you?
I hope this helps you!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hey Andree!
Love that you have shared the little video. Great work. Hopefully you can help fix it.
I'm still having trouble, I'm based in UK England if this helps arrange formula in anyway.
Regards
SD -
I think I've cracked it!
Thanks for your amazing help! -
Happy to help!
Great that you figured it out.
By the way, regarding the form question, you asked previously. Heres another excellent solution. https://www.formstack.com
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 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
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!