Show last modified date or even better history of modifications of listed hyperlinks (also excel books)

[Dave Grimwood]

Hi guys

I have a list of H&S risk reports and I need a formula to help me track the last modified date. I.e I need a column next to the hyperlink to show the modifed date of that hyperlink.

If this could be explained in quite simple terms that would be great.

Cheers! - Dave

 

[Richard Schollar]

Hi Dave

You can't do this with a normal formula - I believe your two options will be:

1. Convert the workbook to Shared in which case you can have it tract changes. Note that there are many issues with Shared Workbooks and they are notorious for becoming corrupt and unusable which is why I never touch them with a 40 foot pole.

2. Use VBA code.

Now, I can help you with (2) above, but I need to know how many hyperlinks do you want to keep track of in this workbook? Also note that one limitation of this option is that macros must be enabled when the user opens the workbook, otherwise it won't work.

 

[Dave Grimwood]

Hi Richard, thanks for such a quick reply.

Option 1 doesnt sound too appealing!!

I will be tracking between 10 and 30 hyperlinks, so not very many.

I get confused with some of the answers on here so if you have a VBA for this please explain it in steps.

Legend!!

 

[Richard Schollar]

No worries Dave I can show you how to do it. One further question though - are these hyperlinks the result of the HYPERLINK function, or are they just typed into the cell eg:

www.google.co.uk

 

[Dave Grimwood]

Thanks Richard!!

They are the result of a hyperlink function.
They are all other excel docs listed with a real pathname.

 

[Richard Schollar]

That could make it a bit more complicated, so further questions:

1. Do these Hyperlink functions reference other cells ie to build up the file you want to link to you might have:

=HYPERLINK(A1,"Some File")

So the file referenced is actually in A1, in which case do you need to track the changes in this cell (rather than necessarily the Hyperlink cell itself)?

Hopefully that makes sense?

 

[Richard Schollar]

And another question to clarify - do you want the last modified date of the actual file that the hyperlink points to, or the date when the hyperlink itself was modified (eg to point at another file) - thanks Andrew for pointing out that!

 

[Dave Grimwood]

No they do not link other cells, just to the document..

\\AlphaStorage\L\OPERATIONS\Projects\CJ Jobs\CJ20791 xx Museum.ALX\Project Management\CJ20791 - xx Risk Register.xls

And I would like to track the changes in a seperate column, if this is what you meant.

Cheers!!

 

[Richard Schollar]

Sorry Dave, what I was asking last was do you wish to record when the file that is pointed to by the hyperlink was last modified (eg when it was saved down last) or when the hyperlink formula itself was last modified? Does that make sense?

 

[Dave Grimwood]

Oh apologies, I want the modified date of the hyperlinked doc, i.e what it points to, not the link itself. Thanks guys