How to reverse the order in which a range is counted?
Hello,
I have the following Smartsheet:
In this Smartsheet, I'm building an asset booking system and, for the 'Asset Available During Your Booking Date Range?' column, it displays whether the particular asset is available for the date range inputted by the user.
My formula is as follows:
=IF(IF([Asset Booking Start Date:]@row = "", "", COUNTIFS([Asset ID:]:[Asset ID:], [Asset ID:]@row, [Asset Booking Start Date:]:[Asset Booking Start Date:], >=[Asset Booking Start Date:]@row, [Asset Booking End Date:]:[Asset Booking End Date:], <=[Asset Booking End Date:]@row) - 1) = 0, "AVAILABLE", "NOT AVAILABLE")
However, the problem is that my formula is counting... bottom up rather than top down if that makes sense? Essentially, in the screenshot I have above, the very first entry with the date range of 06/10/22 and 06/14/22 should state 'AVAILABLE' and the other cells below it, because their date ranges fall within the first entry's date range, should state 'NOT AVAILABLE'. But, for some reason my formula has reversed this and given the newest entry the status of 'AVAILABLE'.
Any help is much appreciated, thank you!
Best Answer
-
Are you adding new rows at the top of the sheet?
There is no way to force the sheet to populate the auto-number column other than saving it. The only way to flag rows where there is overlap and have it flag even without saving to to flag ALL rows where there is overlap at the same time meaning even the first one would change to "UNAVAILABLE". But... If they are just entering the dates real quick and looking to see then I would imagine they would change their dates to find something available before saving anyway?
Answers
-
It isn't so much that it is going from bottom up. It is more an issue with how exactly your formula is written. Try this instead.
Insert an auto-number column with no special formatting. Then use this formula to output availability.
IF([Asset Booking Start Date:]@row <> "", IF(COUNTIFS([Asset ID:]:[Asset ID:], @cell = [Asset ID:]@row, [Asset Booking Start Date:]:[Asset Booking Start Date:], @cell <= [Asset Booking End Date:]@row, [Asset Booking End Date:]:[Asset Booking End Date:], @cell >= [Asset Booking Start Date:]@row, [Auto-Number Column]:[Auto-Number Column], @cell< [Auto-Number]@row) > 0, "NOT AVAILABLE", "AVAILABLE")
-
Hi @Paul Newcome,
Thank you! That formula worked great -- but, I was testing the sheet again with your formula and I noticed the following:
I renamed the 'Auto-Number Column' to 'Asset Booking Priority Rank:' and noticed that, when I deleted all of the rows to create a blank sheet and then tested again, the column appears to start counting bottom up again?
Additionally, when typing in the dates, the 'Asset Booking Priority Rank:' column does not appear to populate until the 'Save' button has been hit -- which then updates the 'Asset Available...' column:
As we have people out in the field, I'm worried that they will just see the asset as being 'AVAILABLE' before hitting save and will assume it is before looking again to see that the asset is in fact 'NOT AVAILABLE' -- is there a way to force the column to update instantly? or potentially a way around using an auto numbering column?
Thank you for your help!
-
Are you adding new rows at the top of the sheet?
There is no way to force the sheet to populate the auto-number column other than saving it. The only way to flag rows where there is overlap and have it flag even without saving to to flag ALL rows where there is overlap at the same time meaning even the first one would change to "UNAVAILABLE". But... If they are just entering the dates real quick and looking to see then I would imagine they would change their dates to find something available before saving anyway?
-
Hi @Paul Newcome,
In response to your question (if I've interpreted it correctly as it's early in the morning here haha), with the current formula it will state that the asset is 'AVAILABLE' even if it is not until the sheet is saved -- changing the asset while the sheet is not saved will not change the asset from 'AVAILABLE'. So, I think I'm just going to have to write an instructions document highlighting the need to save the sheet first before checking to see if the asset is available or not.
I've marked your comment as the answer as, for all intents and purposes, I'd say the current formula does its job and just needs some accompanying documentation
Thank you for your help once again -- I really appreciate it!
Help Article Resources
Categories
Check out the Formula Handbook template!
