Lookup specifics & Match data & record a date between 2 sheets
From a single report sheet (A) in (Grid View) named:
· “Adelaide Annual WHS Calendar”
o Description: (column title)
Various “individual” inspections areas
I am trying to look up in a “Workplace Specific Inspection Register” a range of:
“Business Unit” (column title & dropdown list) As in this case:
· “Adelaide”
And match the relevant “Inspection Checklists” for Adelaide i.e., “AED Defib Machine” of which each checklist entered has an automated “Created Date” @ row.
· I would like to record this date to a single report sheet (A) “Adelaide Annual WHS Calendar”. specific to this item in a static row.
· Under a column titled "Last Date Inspected?” (Date Field)
I have tried a range of functions – Index / Match & Vlookup without success.
If solved this type of function will have really good outcomes for our business with multiple data being collated across many sheets, especially training records & tracking.
Answers
-
I hope you're well and safe!
Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi @Safety Steve,
You can use an INDEX/COLLECT function for this. If you're recording inspections regularly on the Workplace Specific Inspection Register you can also throw in a MAX(COLLECT) for the date criteria.
In your WHS Calendar, list the items & site(s) and use a formula along the lines of:
=INDEX({Created Date},{Item}[Item]@row,{Site},[Site]@row,MAX(COLLECT({Created Date},{Item}[Item]@row,{Site},[Site]@row))),1)
You'll need to set the cross sheet references, but something like this would then pull the maximum (i.e. latest) date for the relevant item on the relevant site. If you have multiple of the same item on a site, you would also need something to distinguish them (e.g. Fire Extinguisher 1, Fire Extinguisher 2 as the Items - probably adding something in the description with location which is which etc.) for the INDEX to work.
Hope this helps at least give you some ideas on how to proceed, but if you've any problems/questions then just post! 🙂
-
Hi Andree,
Many thanks for responding and trying to assist me.
As requested, please find attached screenshots which may help you assist me further.
If I can crack this It would assist me immensely as a common denominator on most of my worksheets.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!