Formulas to Indicate Date and Time Already Scheduled to Trigger Alert
I am trying to create a form for people to use to sign up for a specific date and time (e.g., 12/12/22 @ 11:30 a.m.) to pick up some work equipment.
I created a (1) sign up sheet to capture the form data and (2) a separate sheet containing all dates and times to be scheduled.
In the dates and times sheet, I have a column (Taken) in which I wrote a COUNTIFS formula to indicate the number of people signed up for that date and time; however, only one person can be signed up for each date and time. I want to send an alert to anyone who signs up for a time already taken, but am not sure how to limit the Taken column to only the first person who scheduled that time or how to trigger the alert to send to anyone who schedules a time already scheduled. I created the Unavailable column in the sign up sheet so I could send the alert from that sheet but don't know what formula would work to flag when someone (fx@bing.com) signs up for a time and date already scheduled.
I'm certainly open to ideas to change how I've set things up.
Any help is appreciated!
Lori F.
Best Answer
-
I would insert an auto-number column (called "Auto" in this example) with no special formatting. Then you can use a formula in the flag column to flag the rows that have signed up for something that is already taken like so:
=IF(COUNTIFS([Date and Times]:[Dates and Times], @cell = [Dates and Times]@row, Auto:Auto, @cell<= Auto@row)> 1, 1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
I would insert an auto-number column (called "Auto" in this example) with no special formatting. Then you can use a formula in the flag column to flag the rows that have signed up for something that is already taken like so:
=IF(COUNTIFS([Date and Times]:[Dates and Times], @cell = [Dates and Times]@row, Auto:Auto, @cell<= Auto@row)> 1, 1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you, Paul! That worked perfectly!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!