Lookup - if - and?

Jason P

Hi,

We have a basic sheet using a form, users scan the items QR code, drop their location and select if checking in or out an asset (tick box) and nominate it's return date. Currently we have 100+ assets on the sheet.

I would like the sheet to alert the Proj Manager when the item has not been returned by the due date.

Problem - When using the form sheet sees it as a new entry, there is no way for the sheet to know if it was checked out in the first place or vice versa. I'm sure there's a formula here buggered if I know it though - cheers all.

Paul Newcome

Happy to help. 👍️

Paul Newcome

Are you able to provide some screenshots for reference?

Jason P

Morning @Paul Newcome

Thanks for coming back, Item is column 1 but used only for managed assets below the dark blue line, QR 2 and so on. In the example you can see I have checked out and in an asset.

I would like the sheet to find a checked out asset in the {{QR Code}} when the form user selects check in then after submitting & where a match is found & {{check in}} blank tick the box. Automation wise from this I can then set up a Update to the PM indicating an item is either back or late on return.

Hope this is enough to work from, cheers.

Paul Newcome

Ok. Here is my suggestion...

Insert a new checkbox column. We are going to use a formula to check the box on the "Check Out" row once the item has been checked in. Then you can set up an Automation to trigger when the Estimated Return Date is in the past and the new column is not checked.

=IFERROR(INDEX([Check In]:[Check In], MATCH([QR Code]@row, [QR Code]:[QR Code], 0)), "")

Jason P

Hi @Paul Newcome

I believe I understood you correctly, see attached however the automation did not send an alert that the unit was not back.

Paul Newcome

Have the trigger set to run daily instead of on the Estimated Return Date. The reason that particular setup didn't work is because you are saying to run on the return date where the return date is in the past. If it is running ON the return date then the return date cannot be in the past when the automation runs.

Setting it up to run daily will look at every row based on the criteria and won't require being triggered on a specific date. If there are no rows that match the criteria, then it won't send out any notifications.

Jason P

Thanks @Paul Newcome

Works a treat many thanks, no option to run the automation daily so set up as Run Once 1 day after..

Paul Newcome

When setting up the trigger, click on where it says "Run once" and then click on "Custom".

From there you can switch it over to run every "1 day" which is the same as daily.

Once you set that (click "Done"), you can also choose which hour of the day it runs on back on the main trigger.

Jason P

@Paul Newcome

Nice, thanks Paul - wish I had more time to explore and learn..

Paul Newcome

Happy to help. 👍️