Lookup - if - and?
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.
Cheers.
Best Answer
-
Happy to help. 👍️
Answers
-
Are you able to provide some screenshots for reference?
-
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.
Cheers.
-
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)), "")
-
I believe I understood you correctly, see attached however the automation did not send an alert that the unit was not back.
Cheers.
-
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.
-
Thanks @Paul Newcome
Works a treat many thanks, no option to run the automation daily so set up as Run Once 1 day after..
Cheers.
-
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.
-
Nice, thanks Paul - wish I had more time to explore and learn..
Cheers.
-
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!