Formula to display one date in a range with 3 criteria
Hello everyone,
Can you please help me with a formula?
I have three columns: 'Start date' , 'Dose', 'Site'.
I would like to consider multiple criteria: 1) the start date is in the future 2) the value in the 'Dose' column is 'drug' and 3) the Site is '01'. Based on these criteria there should be a list of dates, but I would only to display one value, the 5th date.
would you have any suggestion on how to do that?
Thank you!
Francesca
Best Answer
-
To pull in the date for the 5th time the site uses the drug, you would use a SMALL/COLLECT combo along the lines of
=SMALL(COLLECT([Start Date]:[Start Date], Site:Site, @cell = "01", Dose:Does, @cell = "drug"), 5)
Are you wanting this to go on every single row, or are you just pulling this one date for site 1 and then pulling a single date for site 2, so on and so forth?
Answers
-
Are you able to provide some screenshots for context? Sample data is fine.
-
Hi,
I hope you're well and safe!
Can you share some screenshots? (Please delete/replace any confidential/sensitive information before sharing.) That would make it easier to help.
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
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.
-
Hello, thank you for your replies, I have attached a screenshot, it is a simplified version of my spreadsheet but hopefully it helps. I would like the formula to display the date highlighted in yellow (then I could apply the same formula for the other sites). Thank you very much
-
To pull in the date for the 5th time the site uses the drug, you would use a SMALL/COLLECT combo along the lines of
=SMALL(COLLECT([Start Date]:[Start Date], Site:Site, @cell = "01", Dose:Does, @cell = "drug"), 5)
Are you wanting this to go on every single row, or are you just pulling this one date for site 1 and then pulling a single date for site 2, so on and so forth?
-
Hi Paul,
THANK YOU SO MUCH for your input, this is working!!
I am pulling this one date for site 1, then pulling a single date for site 2, etc.
Thank you again, have a good day!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!