Lookup specifics & Match data & record a date between 2 sheets

Question

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.

Safety Steve · Answer

Hi Andrée Smartsheet Assistance.docx

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.

Nick Korna · Answer

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! 🙂

Andrée Starå · Answer

Hi @Safety Steve

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!