A way to document the last time a linked sheet was modified?

11/29/21
Accepted

Hello! I have a master project sheet with other sheets linked to it. I am looking for a way to have the master sheet document the last time each linked sheet is modified.

On the attached screenshot, column titled "Project Plan Stage" is where each linked sheet is housed. I am looking to have the column titled "Last Status Update" auto populate a date based upon the last time the linked sheet in Column "Project Plan Stage" is modified.

Thank you!


Best Answer

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    You would need to insert a system generated Modified (date) type column on the Project Plan sheets. Then on the master sheet you would use a MAX function to pull the highest value (using a cross sheet reference) from that column.

    =MAX({Project Plan Modified Date Column}) + ""


    Note: The last bit after the MAX function is converting it into a text value which is what you will need to do to put the formula into a text/number column. If you only needed the date and not the time stamp, then you could wrap the MAX in a DATEONLY function and put it in a date type column on your master sheet. This will allow you to leverage the date for things such as alerts in the event that you wanted to send out automations based on the date of the last update.

    =DATEONLY(MAX({Project Plan Modified Date Column}))

    thinkspi.com

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    You would need to insert a system generated Modified (date) type column on the Project Plan sheets. Then on the master sheet you would use a MAX function to pull the highest value (using a cross sheet reference) from that column.

    =MAX({Project Plan Modified Date Column}) + ""


    Note: The last bit after the MAX function is converting it into a text value which is what you will need to do to put the formula into a text/number column. If you only needed the date and not the time stamp, then you could wrap the MAX in a DATEONLY function and put it in a date type column on your master sheet. This will allow you to leverage the date for things such as alerts in the event that you wanted to send out automations based on the date of the last update.

    =DATEONLY(MAX({Project Plan Modified Date Column}))

    thinkspi.com

  • Thank you so much! 😊

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com

Sign In or Register to comment.