Formula required to show availability of tools
I have been using Smartsheet for a few years now but I am stumped on this one:
I have a list of tools in a drop down e.g. Drone, Shovel, Measuring Tape. Staff request a tool via a SS form. I want the person responsible for managing the tools to be able to see if the requested tool is available straight away when a new request comes in.
So I need a formula in the availability column that will calculate the status.
My initial thought was to have a formula that looks at the item they want to borrow e.g. Drone and then searches the Tool column for instances of Drone and checks that all Date Returned fields against that item are not blank where the Date Taken is not blank. That would mean the tool has been returned and is available.
If the Date Returned field is blank against any instance of Drone and the Date Taken is not blank it means the tool has not been returned and is not available.
Is anyone able to help with the formula for that or should I be looking at trying an Index formula?
Thank you in anticipation for any advice, Tracey
Best Answer
-
I'd say you are on the right track. You would use a COUNTIFS to see how many [Date Taken] are not blank and if that is greater than the number of [Date Returned] that are not blank, then it is unavailable.
=IF(COUNTIFS([Date Taken]:[Date Taken], @cell <> "", [Tool Name]:[Tool name], @cell = [Tool name]@row)> COUNTIFS([Date Returned]:[Date Returned], @cell <> "", [Tool Name]:[Tool name], @cell = [Tool name]@row), "Unavailable", "Available")
Answers
-
I'd say you are on the right track. You would use a COUNTIFS to see how many [Date Taken] are not blank and if that is greater than the number of [Date Returned] that are not blank, then it is unavailable.
=IF(COUNTIFS([Date Taken]:[Date Taken], @cell <> "", [Tool Name]:[Tool name], @cell = [Tool name]@row)> COUNTIFS([Date Returned]:[Date Returned], @cell <> "", [Tool Name]:[Tool name], @cell = [Tool name]@row), "Unavailable", "Available")
-
Thank you so much Paul. That worked perfectly. And I learnt a lot too. I hope you have a fab day :-)
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!