24 July 2006 by Ross McLean
I was surprised to find there was no way to display dependencies arrows for a selection using the Formula Auditing tool bar. I don’t use it a lot, but I would have though that this is the sort of thing it should be able to do. Now, I also know that there are quite a few decent Auditing tools out there which properly give you loads of feature that Excel does ship with, but it’s simple enough to tap in some VBA to get what I wanted”¦ and here it is!
[vba]Sub ForumulaAduitShowPrecedents()
Dim cell As Excel.range
For Each cell In Selection
cell.ShowPrecedents
Next
End Sub
Sub ForumulaAduitShowDependents()
Dim cell As Excel.range
For Each cell In Selection
cell.ShowDependents
Next
End Sub
Sub ForumulaAduitShowClear()
ActiveSheet.ClearArrows
End Sub[/vba]
Straight forward stuff, the result will look some thing like this:
Here’s a little tip, if the arrows are too grouped up make the coloums a little wider – what an insight!
Â
Tags: Best Practice, Examples, VBA
Categories: General, Spreadsheet Design •
No Comments »
11 July 2006 by Ross McLean
Recently I have been thankful for a couple of Office features which help with damaged files. I think both of the following first appeared in Office XP. The first is extremely strange and was unknown to me until very recently.
On the “About Microsoft Excel” (under the help menu) there is a “Disabled Items” button. Why the hell it’s there I don’t know, I don’t think man will ever find out, but there it is none the less. It pops up a box of workbooks that Excel has “Disabled”, normally because they are causing Excel to crash. You can (re)enable them there too.
 
The next is a slightly more logical feature but still often overlooked. Sometimes a file corrupts and Excel won’t open it. From Excels’ Open Dialog, select the problem file and click the drop down arrow on the open button. Then click “Open and Repair”.
Still no joy? You might try opening your file with OpenOffice.org, which has a decent reputation for recovering damaged documents.
Tags: Examples
Categories: General •
No Comments »
5 July 2006 by Ross McLean
This is very cool! and as it’s the world cup!!!!
Â
Â
http://www.xlfootymanager.co.uk/
Tags: Games
Categories: News! •
No Comments »
4 July 2006 by Ross McLean
It struck me today that the DGET function might be the most useless fuction in the whole of Excel, this is from the help file:
DGET
Extracts a single value from a column of a list or database that matches conditions you specify.
Syntax DGET(database,field,criteria)
…
Remarks
If no record matches the criteria, DGET returns the #VALUE! error value. If more than one record matches the criteria, DGET returns the #NUM! error value.
Â
So it basically returnes one cells if the criteria happens to be the ONLY one that matches what you have specified. What gets your vote for the worst Function?
Categories: General •
4 Comments »
