Formula Auditing by RefTreeAnalyser: Objects included
Jan14
on January 14, 2014
at 8:53 am
Posted In: Around the Web, Data Validation, Downloads, Excel Intermediate, Formulas, MVP, Navigation, Pivot Tables, Worksheet Functions
Hi all,
I’ve been working on my RefTreeAnalyser again. What I’ve been up to this time is building tools which help with the analysis of dependencies which are mostly hidden from view:
- Charts (series formula)
- Pivot table (source data)
- Data Validation formulas
- Conditional Formatting formulas
- Form controls (linked cell, listfillrange)
- ActiveX controls (linked cell, listfillrange)
- Picture objects (linked cell)
A new dialog has been added that shows all sources of the objects in your file:
Moreover, when you analyse a particular cell for its dependencies, objects are taken into account too (well, to be perfectly honest, only if you purchase a license):
If you haven’t already done so, why don’t you head over to my website and download the tool. The demo is free and (almost!) fully functional.
Regards,
Jan Karel Pieterse
www.jkp-ads.com
Cells can contain two types of hyperlinks. There’s the embedded kind that you create using Insert – Hyperlink and the formula kind that you create using the HYPERLINK function. The function kind is nice because you can make the address and display text dynamic without using VBA. They’re just text arguments to a function and any function that modifies text can be used to modify them.
If you have HYPERLINK in a cell, the Insert – Hyperlink control is disabled (grayed out). Excel is wise enough to know that you shouldn’t have both kinds of hyperlinks in a cell. But it’s only half wise. Excel does not stop you from entering a HYPERLINK formula in a cell with an embedded hyperlink. If you do, you can end up with what seems like two hyperlinks in one cell.
I say “seems like” because Excel only recognizes one. And to be even more precise, it recognizes pieces of both hyperlinks to make one. Let me explain. If I type a URL in a cell, Excel converts it into a hyperlink. (Pro Tip: Press Ctrl+Z immediately after the conversion to undo the conversion, but keep the text). Let’s say I copy that down a few cells.
Now let’s say that I edit these cells to contain a HYPERLINK formula with a different address and a different display text. In this case, I’ve change the address by adding “my” in front of it and change the display text from the URL to the word “blog”.
If I hover over the new hyperlink, check what happens. There are three important properties of hyperlinks: Address (where it goes when you click), Text to Display (what shows up in the cell), and Tooltip (what pops up when you hover). With two hyperlinks, it appears that the Address and tooltip are driven by the embedded hyperlink and the Text to Display is driven by the formula.
I don’t know why it happens this way. I can’t even come up with a good story about how it’s an unintended consequence of some design decisions on Microsoft’s part. But it is what it is. I don’t know of any quick way to fix this through the user interface, but I wrote a macro to fix it.
Sub RemoveAndUpdateHyperlinks()
Dim rCell As Range
For Each rCell In Sheet1.Range("A2:A10").Cells
On Error Resume Next
rCell.Hyperlinks(1).Delete
On Error GoTo 0
rCell.Formula = rCell.Formula
Next rCell
End Sub
Dim rCell As Range
For Each rCell In Sheet1.Range("A2:A10").Cells
On Error Resume Next
rCell.Hyperlinks(1).Delete
On Error GoTo 0
rCell.Formula = rCell.Formula
Next rCell
End Sub
The code removes the embedded hyperlink and leaves the formula. The line that sets the formula equal to the formula is get the blue underline formatting back. The traditional hyperlink formatting disappears when you delete the hyperlink even though the formula remains. You can see that the tooltip now draws from the only remaining hyperlink, the formula one.
