If Statements
Hello All,
I am attempting to create an If statement that raises a flag if two rows have the same location and the same date.
This submission sheet will be used when people visit a location and we do not want any double bookings. So I was planning on having a formula raise a flag and then have a report that looks for the flag to put on our dashboard.
The sheet is very simple, just a list of location names in one column and the date the person is planning on visiting that location, there are several hundred locations to choose from.
Appreciate the help or advice if you think I am going about it the wrong way.
Thanks
Best Answer
-
Hi @Joseph Noel
You should use either one of these formula in your flag column:
=IF(COUNTIF([Location]$1:[Location]@row, [Location]@row, [Date]$1:[Date]@row, [Date]@row)>1, 1, 0)
This will raise a flag if someone book a date and location already booked.
Other alternative is:
=IF(COUNTIF([Location]:[Location], [Location]@row, [Date]:[Date], [Date]@row)>1, 1, 0)
This will flag everytime a Location and Date is selected more than once.
Choose either depending on how you want the flag to be raised :)
Hope it helped!
Answers
-
Hi @Joseph Noel
You should use either one of these formula in your flag column:
=IF(COUNTIF([Location]$1:[Location]@row, [Location]@row, [Date]$1:[Date]@row, [Date]@row)>1, 1, 0)
This will raise a flag if someone book a date and location already booked.
Other alternative is:
=IF(COUNTIF([Location]:[Location], [Location]@row, [Date]:[Date], [Date]@row)>1, 1, 0)
This will flag everytime a Location and Date is selected more than once.
Choose either depending on how you want the flag to be raised :)
Hope it helped!
-
@David Joyeuse Thank you. I had googled it and got a similar formula that didnt work, thank you this worked perfect. Also thanks for the quick response.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!