Compare hyperlinks

DougBob

New Member
Joined
Dec 1, 2005
Messages
5
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
Doesn't your existing formula pick up differences in hyperlinks? Like this:
Book1
ABCD
1www.mywebsite.com/TRUE
2www.mywebsite.com/
3
4
5www.mywebsite.comFALSE
6www.mywebsite.com/different
7
Sheet2


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

DougBob

New Member
Joined
Dec 1, 2005
Messages
5
Correct, the value doesn't change in the cell, but the link to a document does.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,222
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

New Member
Joined
Dec 1, 2005
Messages
5

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,222
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

New Member
Joined
Dec 1, 2005
Messages
5

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,222
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

New Member
Joined
Dec 1, 2005
Messages
5
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,451
Messages
5,572,201
Members
412,447
Latest member
immy
Top