Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects

Formula reference auditing tool

This is an example of a tool I developed for a US-based company.

(The tool is now commercially available too! Check out this page for more information and a free demo)

As you probably know, Excel has built-in formula auditing tools (see the accompanying toolbar below).

With these tools, one can ask Excel to show arrows on the worksheet which point to precedent cells or dependent cells of the currently active cell. The example below shows that this is very useful.

However, if one wants to navigate the reference tree, things become cumbersome. For example, the figures in the range D2:D4 may also be calculated, using ranges on other worksheets. For example, highlighting cell D1 and pressing the show precedents button then gets you the arrow (the one with the tiny table) that indicates off-sheet references are involved:

Double clicking an off-sheet reference arrow will show a dialog box with all precedents on other worksheets. Unfortunately, when there are references involved to other workbooks, the entire path of a reference cannot be read (the box cannot be resized or scrolled, unless one clicks a reference, clicks in the "reference box" and uses the arrow keys to scroll the reference).

The tool I wrote for my customer eases this process considerably. It will show all precedents/dependents (or both) in a single (resizable) dialog screen in a well structured tree:

When one clicks any entry in the tree, the accompanying range of cells is selected automatically and if the reference is present in the formula of the starting cell, this part of the formula is highlighted.

Request more information

If you're interested in this tool, get in touch and we'll discuss the possibilities.

Please enter your name (required):

Your e-mail address (mandatory; will be used to contact you):

Your question or remark: