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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!