Compare hyperlinks

[DougBob]

Using techniques learned here, I am comparing the sells on subsequent day's copies of a sheet. I can see what has changed in the content, but
I have the need to detect when a hyperlink is added or changed on a cell.
I am referencing all the cells on sheets named in some control cells: (AC1 & AD1)
=IF(INDIRECT("'"&$AC$1&"'!"&CELL("address",A2))=INDIRECT("'"&$AD$1&"'!"&CELL("address",A2))," ",1)
which generates blank or 1, if the cell is the same or not.
How do I add whether the hyperlink in the cell is changed?

Thanks, in advance, Doug

 

[PA HS Teacher]

Doesn't your existing formula pick up differences in hyperlinks? Like this:

Book1
	A	B	C	D
1	www.mywebsite.com/			TRUE
2	www.mywebsite.com/
3
4
5	www.mywebsite.com			FALSE
6	www.mywebsite.com/different
7
Sheet2

Are you using the hyperlink function (with a friendly name that doesn't change)?

 

[DougBob]

Correct, the value doesn't change in the cell, but the link to a document does.

 

[Tom Urtis]

Hi, just thinking out loud, have you considered a UDF for this. Example, if one hyperlink is in cell AC1, and the other is in AD1, with both values in those cells being the same but you want to compare actual hyperlink addresses to see if they match, this might do it.

Place this UDF code in a standard VBA module:

Function CompareLinks(Hyp1 As Range, Hyp2 As Range) As String
Application.Volatile
If Hyp1.Hyperlinks(1).Address = Hyp2.Hyperlinks(1).Address Then
CompareLinks = "Matching hyperlinks"
Else
CompareLinks = "Non-matching hyperlinks"
End If
End Function



Then, in some other cell on the worksheet, enter this formula:

=CompareLinks(AC1,AD1)

 

[DougBob]

This function works if the cells I am comparing are on THIS page. If they are an indirect reference to THIS page, they also work. But when I point them to another page (sheet) it errors. Any ideas? Thanks, Doug

 

[Tom Urtis]

Hmmmm....the UDF works for me on the active sheet or from another sheet while referring to cells on the subject sheet. Example, if cells AC1 and AD1 are on Sheet3 and I go to Sheet1 and in some cell on Sheet1 I enter
=CompareLinks(Sheet3!AC1,Sheet3!AD1)
then the UDF still works.

Since that is apparently not working for you, I am probably misunderstanding the INDIRECT issue because so far the UDF has been pointing to a cell that supposedly has a hyperlink and I see now you are using INDIRECT to point to cells that point to other cell addresses where the hyperlinks are (?).

Can you please give an example of what is in AC1 and AC2, is it simply cell addresses, like J5 or B6 and the parent sheet name is inferred, or do you have Sheet2!J5 and Sheet4!B6 in AC1 and AC2 respectively for example. Sorry, I should have clarified that for myself earlier before posting the UDF.

 

[DougBob]

AC1 & 2 have the sheet names for the other sheets.
The individual cells contain the comparison,indirect though them, so I can compare hundreds of rows on the sheets by changing the pointers.

Maybe there is a much easier way to do this?
I create a copy of a sheet each day, and want to be able to flag what cells have changed. It is convinient to be able to compare any two sheets, so I can see what changed for the last week or month.

 

[Tom Urtis]

Well, let's see...just the sheet names are in AC1 and AD1 of a certain sheet, and you want to compare hundreds of rows, which presumably means hundreds of pairs of hyperlink addresses in those cells by "changing the pointers".

You asked if maybe there was a better way to do what you are doing, and maybe there is, it sounds on the surface like there might be a better way. Maybe it's me with the week it's been, but I don't think I have a grasp on the set-up yet. Example, where are the cell addresses being referenced? Are they just cell references in other cells to other hyperlink-containing cells, or are they the actual cells containing the hyperlinks? Do you maybe have a 2-column list of hundreds of hyperlinks, such as in column A and column B of some sheet, and you want the comparison of addresses to be in column C? If so, what sheet? If not, are these hundreds of hyperlinks scattered throughout the workbook? Or, are they just 2 cells that you need to evaluate hundreds of times each?

Maybe if you provide more details about your workbook set-up, I or someone can offer a solution that is relative to what you are working with, just not sure yet what the workbook structure is.

 

[DougBob]

You are right, let me explain the usage. I am tracking a software project.
I have a spreadsheet containing about 400 individual pieces of
the project. Across the top are the various stages for each piece (design, code, test, docs) with multiple colums for each (date, person, % done). Yes, I know Project might be better, but I'm not there yet.

In the name column of each phase (where the individual's name appears) I hyperlink to the defining document (design spec, MS Project plan, Doc outline, test plan, etc) for that phase. These things can change as the project does, or as people move, or whatever.

I save this sheet at the end of each day (ie, copy the sheet, and rename to the next date) so we have the history of the project.

I have a "compare" sheet, which is filled with the comparisons from each cell from one dated sheet to another. These comparisons are indirected to the particular sheet. For example, the pointer cells (AC1, AD1) might have 11-30 and 12-5 in them, so all the cells on those two sheets are compared.

The results of the compare are a 0 (when equal) or a 1 (when different) so
I can show the number of rows or columns that changed. This works fine for the values (or even formulas) in the individual cells. But the compare doesn't detect when the hyperlink attached to a cell changes.
=IF(INDIRECT("'"&$AC$1&"'!"&CELL("address",A2))=INDIRECT("'"&$AD$1&"'!"&CELL("address",A2))," ",1)

I added the function suggested above, which I add to the compare as in:

=IF(AND(INDIRECT("'"&$AC$1&"'!"&CELL("address",A2))=INDIRECT("'"&$AD$1&"'!"&CELL("address",A2)),CompareLinks(INDIRECT("'"&$AC$1&"'!"&CELL("address",A2)),INDIRECT("'"&$AD$1&"'!"&CELL("address",A2))))," ",1)

This gives a #VALUE error. If I explicitly replace the indirects in the function calls with local cell names, it works fine. I can even use indirects if they point onto cells within this compare sheet. But when I try to compare the cells from two different sheets, it fails.

I would be happy to email the whole workbook to you, if you want it. I REALLY appreciate the help given so far. Thanks, Doug