July 2006

“Auditing” a whole range

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:

arrows.JPG

Here’s a little tip, if the arrows are too grouped up make the coloums a little wider – what an insight!

 

Broken Workbooks!

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.

 Disabled Items.JPG

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”.

Open and Repair.JPG

Still no joy? You might try opening your file with OpenOffice.org, which has a decent reputation for recovering damaged documents.

xlfootymanager

This is very cool! and as it’s the world cup!!!!

 

xlfooty.GIF

 

http://www.xlfootymanager.co.uk/

What is the most useless Excel Function

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?