Formula required to show availability of tools

Tracey McNaughtan

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

Paul Newcome

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")

Paul Newcome

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")

Tracey McNaughtan

Thank you so much Paul. That worked perfectly. And I learnt a lot too. I hope you have a fab day :-)

Paul Newcome

Happy to help. 👍️