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

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

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
Home > English site > Products > RefTreeAnalyser
Deze pagina in het Nederlands

RefTreeAnalyser: The ultimate Excel Formula Auditing tool

For Microsoft® Excel®

Excel formula auditing taken to the next level!

Features:

Ordering

A single user license for RefTreeAnalyser costs € 30 (excluding local VAT).

A license includes:

Buy RefTreeAnalyser now!

Prices

Number of licenses Price per licence
1-10 € 30.00
11-50 € 23.00

Contact me if you'd like to order more than 50 licenses

Free Download

Use the links below to download the tool.

The tool will work without a license code but with (only slightly) limited functionality. After purchasing a license, you can simply enter the license code into the tool's settings dialog. The limitations will be removed instantly.

Current Excel versions (Excel 2007 and up)

Download RefTreeAnalyser for Excel 2007 to 2016 (Free demo!!!) V2.0 (Build 122, Updated Nov 23, 2017, downloaded: 10,638 times)

Excel 2003

Please note that the 2003 version will no longer be updated!

Download RefTreeAnalyser for Excel 2003 (Free demo!!!) V2.0 (Build 082, Updated Dec 15, 2015, downloaded: 1,188 times)

Blocked files problem

Recently (I write this in August 2016), Microsoft has "enhanced" security and is now blocking any files that are downloaded from the internet. This means that any add-in you try to open after downloading will simply not open in Excel (without display of a warning message!). To resolve this, follow these steps:

1. Copy the add-in from the zip file to any folder

2. Right-click the xla(m) file and choose properties:

Select file properties

3. Click the Unblock button:

Click Unblock

Money back guarantee

If you are in any way dissatisfied with RefTreeAnalyser, contact me within 30 days after your purchase and I'll issue a refund.

Buy RefTreeAnalyser now!

What is RefTreeAnalyser?

Ever had to work out the logic of other people's Excel files? Ever had to untie the spaghetti-knots of a large Excel workbook's formulas? Then you know what a nightmare this can be!

Now there is the RefTreeAnalyser, the must-have expansion of your Formula tab of the Ribbon!

Screenshot of RefTreeAnalyser Visualize option of RefTreeAnalyser

With this tool, finding out how a cell in a workbook derives its results and what other cells depend on the cell is a breeze. RefTreeAnalyser makes tracing precedents and dependents a lot easier than Excel's built-in tools. Read more about RefTreeAnalyser.

Supported Excel versions

RefTreeAnalyser is designed to work with Excel 2000 and up, including all 64 bit Excel versions.

More information

Buy RefTreeAnalyser now!

 


Comments

All comments about this page:


Comment by: Doug Glancy (12/17/2007 1:49:47 PM)

Jan Karel,

Today I had need for such a tool so I downloaded the demo. I clicked on a cell and
chose "dependents". It started going through every row of another sheet which has
about 1,300 rows. It was taking quite a while, so I hit stop and after about a
minute it did. I wonder if Application.ScreenUpdating would speed things up?

I then ran the uninstall from Add/Remove progams. It reported success, but the
2-icon toolbar was still in Excel. When I clicked on the left-hand button, I got the
same message I'd get if I chose Tools>Formula Auditing>Trace Dependents. I deleted
the toolbar in Tools>Customize and it's gone for good.

I have 2 versions of XL on this computer 03 and 07. The toolbar is only present in
03 - I'm not sure if it was ever installed in 07.

I thought you might like to know this.

Doug

 


Comment by: Jan Karel Pieterse (12/17/2007 10:24:07 PM)

Hi Doug,

Well, some food for thought there obviously. Screenupdating IS turned off as far as
I know, but I'll double check. I also know about the toolbar problem and this is
scheduled to be fixed in a later version.
In 2007 you should look on the Formulas tab, no toolbar there.

 


Comment by: Kristi (2/25/2008 3:26:25 PM)

It would be helpful to see a screenshot of the report...

 


Comment by: Kevin H. Stecyk (4/7/2008 6:24:23 AM)

I really enjoy using your program. I've been using it steadily, and it certainly
helps reduce the burden of understanding someone else's mess, I mean spreadsheet.

 


Comment by: Jan Karel Pieterse (4/8/2008 12:53:21 AM)

Hi Kevin,

Thanks!! Much appreciated.

 


Comment by: John Russell (7/4/2008 8:34:07 PM)

Hi, I have just installed your demo version of RefTreeAnalyzer and on opening Excel
I get the following error:

Oops, An error has occurred in RefTreeAnalyzer
Error:
Error 13: Type mismatch in modMenu.CreateMenu
Abort - Retry - Ignore

Retry doesn't help so I have gone for Ignore and it appears to work, although it
displays my RC references as A1 refs which is confusing.

I am running Excel 2003 SP3, XP SP2.

Would appreciate any suggestions you can offer.

 


Comment by: Jeff K (9/22/2008 4:47:46 PM)

Hi,

This is a really great tool. I just started using the paid version, and it is
incredibly helpful for me to debug spreadsheets.

I believe that there is a defect whereby a sheet name containing a single quote
(e.g., FTE's_Single Site) is not 'auditable' (i.e., when a cell from this sheet
shows up in Dependants, clicking on it does not take me to the cell). When I remove
the single quote from sheet name, all works fine. Please fix, if possible.

Thanks.

 


Comment by: Jan Karel Pieterse (9/23/2008 1:25:00 AM)

Hi Jeff,

Thanks for the kudo's.
I'll look into the quote problem, definitely a bug.

 


Comment by: Norm (5/26/2010 7:46:38 AM)

I purchased your reftree analyser Feb 19 2009.
Even with it's quirks, it's a handy add-in to have around.

Is there anyway I can determine the license no. of the installed program.

I'm transitioning to a new computer and want to re-install reftree.

 


Comment by: Jan Karel Pieterse (5/26/2010 8:09:52 AM)

HI Norm,

You should have an email from 'webmaster at jkp-ads.om' which contains your registration code.
If you misplaced the message, send me an email (see address below) and I'll re-send it to you.

 


Comment by: Sue (7/26/2010 9:21:21 AM)

I bought reftreeanalyser last year and find it very useful.

Occanionally Excel crashes and the menu has dissapeared. However I but cannot get it to appear anymore though there is no error meesage when I download it and enter the licence key. And Reftreelauncher is listed under available Excel addins.

I am using a new windows 7 machine now, and office 2007.

Grateful for any suggestions here,

Sue

 


Comment by: Jan Karel Pieterse (8/16/2010 3:10:05 AM)

Hi Sue,

Maybe the addin has been disabled by Excel. Check:

Office button, Excel Options, Addins tab, in the dropdown, select "Disabled Items" and click the Go button. See if the reftreeanalyser is in that list. If so, select and click enable.

 


Comment by: Nikhil Mehta (8/23/2010 12:45:35 AM)

I am using your tool for MS Excel spreadsheet in Windows. Do you have the same tool for MS Excel for MAC.

 


Comment by: Jan Karel Pieterse (8/23/2010 3:53:06 AM)

Hi Hikhil,

I'm sorry, this addin is only available for the Windows version of Office.

 


Comment by: William McNair (11/19/2010 9:36:44 PM)

Any word on Excel 2010 64bit version. I started using this add-in on Excel 2007 32bit and now can't live without it. If needed, I will pay for an upgrade!

 


Comment by: Jan Karel Pieterse (11/21/2010 10:21:44 PM)

Hi William,

I'm working on a 64 bit version, but it is not ready yet. It will take at least a couple of months to reach beta stage.

 


Comment by: Andrew Hooks (1/18/2011 2:43:35 AM)

Is there any way to get your RefAnalyser tool to work on a locked PC? The Setup.exe is blocked but I thought I might be able to copy the files manually.

Thanks,
Andrew

 


Comment by: Jan Karel Pieterse (1/18/2011 3:45:29 AM)

Hi Andrew,

Well, I could send you the dll, but you would still need administrator privilidges to register the dll on your system.
And then you would also have to put the proper files in place (there is a helper Excel add-in involved to register some shortcut keys which is also needed and a Help file).

This is what the installer takes care of.

 


Comment by: CHANDRU (5/3/2011 5:25:08 PM)

Hi, I want to know how to nest different conditions in single cell. Eg., Row A1=X, Row B1=Y, Row C1=Z the row D1 = Autopopulated. If Rows A1, B1 and C1 are dropdown lists, how to nest and autopopulate in row D1 for possiblities in rows A1, B1 & C1... currently, all conditions are in different rows with formula =IF(AND(A1="A",B1="R",C1="T"), "Template Name","Error")

I would like to know if your tool can help me out on this function.

Regards,
Chandru

 


Comment by: Jan Karel Pieterse (5/4/2011 12:00:28 AM)

Hi CHANDRU,

My tool is built to help you analyse from where a cell derives its results and which cells depend on the current cell. It does not help you create a function.
From your message I suspect you are asking how to create a cell formula that achieves a certain goal, but I am not sure I understand what the formula has to do exactly.

 


Comment by: Fabio (10/28/2011 11:36:12 AM)

Hi, When do you think the 64 bit version would be available?

I just installed Office 2010 and I am desperate!!

Thanks, Fabio

 


Comment by: Jan Karel Pieterse (10/31/2011 1:09:13 AM)

Hi Fabio,

Not any time soon I'm afraid.
Why have you installed 64 bit Office if I may ask?
Unless you are working with huge (and I mean huge) Excel files, there is not much benefit in using 64 bit Office.

 


Comment by: Bernard Lebelle (11/4/2011 2:33:11 AM)

Does it work on Macintosh ?

 


Comment by: Jan Karel Pieterse (11/4/2011 5:05:53 AM)

Hi Bernard,

Unfortunately not!

 


Comment by: yves labidoire (1/31/2012 12:13:10 AM)

Hi,
Here is what i need and I think your tool could help but not 100 sure.
from one cell i need to know the entire fully exhaustive dependency tree. why it seems is what this tool does. But then i would like to recalculate the entire tree in excel. for this i need acess in text flie to all the data so that i can ask excel to recalculate all cells.
thx
Yves

 


Comment by: Jan Karel Pieterse (1/31/2012 6:06:47 AM)

Hi Yves,

The tool gives you the precedents/dependents of the active cell in a number of levels you can select.
The max number of levels is set to 5 however, as otherwise you may end up waiting forever for the tool to finish. Also, the dependency tree becomes very difficult to understand with so many levels.
I'm not sure I understand why you need the text file?

 


Comment by: Robert (2/9/2012 6:26:59 PM)

Jan,

This is a great tool! I'm currently using the demo version but am thinking of upgrading to the full version. One question - when using the tool in tile mode, is it possible to return focus to the spreadsheet window using the keyboard and without closing the tool window? I have tried "ALT + TAB" but I can't seem to go back to the spreadsheet window after activiating the tool. I ask as I use keyboard shortcuts alot and it would be good to keep the tool window up while auditing formula to avoid the windows resizing every time the tool is activate.

Thanks,

Robert

 


Comment by: Jan Karel Pieterse (2/10/2012 12:57:23 AM)

Hi Robert,

Unfortunately there is no shortcut for this yet. Good suggestion though, I'll put it on the list of improvements.

 


Comment by: Dan C (4/17/2012 1:40:27 PM)

Can you give an update for when this will be available for the 64 bit version of Excel? I've read about this product and think it could help, but I just switched to the 64 bit 2010 version of Office.

Weeks/months/years if you were to guess?

 


Comment by: Jan Karel Pieterse (4/17/2012 9:20:33 PM)

Hi Dan,

Good question! So far, work-for-hire has been getting in the way time and again for the past year or two, so it is probably years rather than months.

I could (just for you) port the solution back to an Excel add-in, making it 64 bit compatible. But you'd pay the hours I would need.

 


Comment by: Dan C (4/27/2012 10:36:16 AM)

My IT dept confirmed that they wouldn't be able to switch me to 32 bit, so I am out of luck I'm afraid... If you had to guess, how many hours/$ per hour would that endeavor take?

 


Comment by: Jason (2/26/2013 9:18:27 AM)

Is there a 64 bit version that will be made available?

 


Comment by: Jan Karel Pieterse (2/26/2013 11:14:38 AM)

Hi Jason,

Not yet, but I am working on one.

 


Comment by: Jason (5/3/2013 1:57:15 AM)

This is awesome Jan. Love the update for 64 bit Excel. Is there a way to customize the keyboard shortcut key that launches the tool? Thanks

 


Comment by: Jan Karel Pieterse (5/3/2013 11:29:23 AM)

Hi Jason,

Thanks for the compliments, much appreciated.

I'll add this one to the update wish-list.

 


Comment by: Jason (5/4/2013 1:39:04 AM)

Another point is that when I call the reftree, the box comes up in the middle of the window. I tried resizing and changing the location to the right of the window. However, every time i call the reftreeanalyzer, it comes back up in the middle, where it blocks the spreadsheet. Just a thought

 


Comment by: Jan Karel Pieterse (5/4/2013 10:45:32 AM)

Hi Jason,

Thanks. I guess you want the tool to remember its window position. It used to do that, but I had to make changes related to Excel 2013 to make certain things work properly. I'll revisit that code some day.

If you click the update button on RefTreeAnalyser, does it say there is a new versions?

 


Comment by: Jason (5/5/2013 10:20:21 PM)

Jan -

I just updated my version and saw that I can now customize the shortcut keys, which is great. The one issue I am having is that when I press [ Ctrl Shift - ], it brings up the reference tool even though its not set to that shortcut. Any thoughts?

Yes, if the tool could remember the window position, that would be pretty handy, given the window can actually block the dependent cells I am referencing to.

 


Comment by: Jason (5/6/2013 8:35:26 AM)

Yes I updated it but I still have a problem. When I press Ctrl Shift - it opens the reftree analyzer window and I can't change that.

As I begin to use the tool more (I had previously used it in prior versions of Excel), the window position is pretty important I think as the window is constantly blocking the cells that I am trying to audit.

Thanks for the quick response

 


Comment by: Jan Karel Pieterse (5/6/2013 9:49:11 AM)

Hi Jason,

Does it register your new shortcut keys properly next time you start Excel perhaps?

What if the tool would remember the "Tile" checkbox value on the form, would that help?

 


Comment by: Jason (5/7/2013 10:07:30 PM)

I just updated the plugin and the window now remembers its location which is FANTASTIC. this is really great stuff Jan.

the only minor issue I still have is [ Ctrl Shift - ] still brings up reftree even though that shortcut isn't registered.

Thanks

 


Comment by: Jan Karel Pieterse (5/8/2013 10:28:17 AM)

Hi Jason,

This is really odd, as it does not do this for me at all!
Is it possible that you still have an add-in called RefTreeLauncher installed (a remnant from V1)?

 


Comment by: Craig (5/27/2013 11:32:25 AM)


I've tried installing the trial version of RefTreeAnalyser into Excel 2007, but on adding it I get the error message "Run time error 424 - Object Required", and the add in fails to be applied.

Any ideas how I can solve this?

Thanks!

 


Comment by: Jan Karel Pieterse (5/28/2013 8:51:19 AM)

Hi Craig,

Can you please try build 019?

 


Comment by: Rene (6/3/2013 12:32:42 PM)

I am getting the same run time error 424 as above all of a sudden (after the latest updated).
In the past I could use it after clicking away this message but now (after re-installing the above version) I cannot even use the tool anymore with the error coming up again.

Any progress / ideas on this?

 


Comment by: Jan Karel Pieterse (6/3/2013 4:06:25 PM)

Hi Rene,

Please download the latest build, it should resolve the error.

 


Comment by: Rene (6/3/2013 4:47:28 PM)

No more probs.

Cheers, man.

 


Comment by: David (8/12/2013 11:51:07 PM)

I updated to the latest build, but it set my Excel formula setting from autorecalc to manual. Really ignoring.

 


Comment by: Jan Karel Pieterse (8/13/2013 7:34:50 AM)

Hi David,

Apologies, bug fixed.

 


Comment by: Dave Hockin (10/29/2013 1:04:29 PM)

Jan, I 've tried on 3 occasion to get you to reply for the new key for the 2.0 upgrade.On Apr 24-13 you sent the email and I've replied on Apr 25, Apr 30, and may 24th with no response. Can I get the upgrade and the key ?


In my email I've supplied you with the licence key.
What is the process that I should follow?

 


Comment by: Jan Karel Pieterse (10/29/2013 4:23:40 PM)

Hi Dave,

Apologies for not responding to your messages, perhaps they were eaten by my spam filter, I don't know, because I cannot find any of them!

Just fill out the order form above. As a V1 user you are entitled to a 50 percent discount on this version.

 


Comment by: Wouter Mense (11/4/2013 11:42:55 AM)

Hello Jan,

I was evaluating the trial version of your tool with one of my workbooks. So far everything looks great! However, when tracing precedents for some cells, I am getting a

Run-time error '-2147221503 (80040001)':
Duplicate key: 'Precedents034¶'[calc.xls]Sheet 03'!O49¶ROOT'

Looks like a custom error message from the VBA code, so maybe you would be willing to help me out with it even though I am not a paid user?

Regards,

Wouter Mense

 


Comment by: Jan Karel Pieterse (11/4/2013 12:37:10 PM)

Hi Wouter,

Thanks for letting me know, I'll look into this.

 


Comment by: Dave Hockin (11/7/2013 8:26:15 PM)

Ver 2.042
Help file will not display.(Had same issue for build 041)
Chm file in the same folder as reftreeanalyserxl.alam
also in the folder is the xxx.chw file (all the files from the zip in same folder)

When I click on help, the menu comes up, the topic list is clickable but explorer reports back msg of "Navigation to the webpage was canceled"
Using Explorer 8.0.7601.17514CO

 


Comment by: Jan Karel Pieterse (11/8/2013 7:39:44 PM)

Hi Dave,

This is a windows issue, you have to rightclick the chm file and enable it somehow.
See: http://support.microsoft.com/kb/902225

 


Comment by: Kanwaljit (11/21/2013 11:05:52 AM)

Hi Jan,

I tried to fill the form but I am not able to specify any quantity in No of Licenses drop down as it does not contain anything.

Regards
Kanwaljit

 


Comment by: Jan Karel Pieterse (11/21/2013 1:53:56 PM)

Hi Kanwaljit,

I have fixed the issue with the number of licenses, thanks for bringing this to my attention!

 


Comment by: Eileen (12/4/2013 7:06:10 PM)

We have noticed that when using Edit/Replace a window box for Replace has a default to find "42" and that "Match entire cell contents" box is checked. This started to happen when we updated RefTreeAnalyser to Version 2. This seems to happen to different versions of Office (2010 and 2013; 32- and 64-bit).

Is this a bug with a future fix?

 


Comment by: Jan Karel Pieterse (12/5/2013 7:48:16 AM)

Hi Eileen,

Thank you for letting me know. I am aware of the issue and a fix is already in beta stage. You should be receiving an update within a couple of weeks

 


Comment by: Charlie (1/9/2014 7:15:37 PM)

I noticed in your latest update notice that you mention the tool works with all excel objects including data validations - I tried it on a dropdown list for a data validation and it did not indicate the cells that were using the dropdown list as dependents - what am I doing wrong?

Love the tool - thanks

--Charlie

 


Comment by: Jan Karel Pieterse (1/10/2014 11:14:26 AM)

Hi Charlie,

Thanks for your feedback!

Can you please show the data validation formula (which points to the list)?
Also: which Excel version are you using?

 


Comment by: Dave Hockin (4/10/2014 1:40:09 AM)

Bug: Ver 2.0 Release 052, Excel 2010 version 14.0.6129.500 32 bit

Cannot disable default hot key. Specifically Objects is firing using Ctrl + Shift + O which I have mapped to one of my own macros.I have unchecked the box in Settting, and tried full Excel shut down and restart. No effect



All other Hotkeys seem to be editable

 


Comment by: Jan Karel Pieterse (4/10/2014 9:54:08 AM)

Hi Dave,

What if you change it again and then restart Excel?

 


Comment by: Dave Hockin (4/10/2014 2:22:56 PM)

As noted the issue is for Object key only.

I found a sequence that is a workaround
-assign a new letter to eg X or leave it blank
-close excel and restart

when assigning X, it is now the key that is locked to Objects and my existing Shift + Ctrl + H can be run

However as with H, X is now stuck with or without the checkbox on the setting tab. Only way to release X is to have another letter assigned to it or leave it blank.

Overall it seems that the checkbox is NOT deactivating the Hotkey for this case. I myself have found
Application.MacroOptions buggy at times so perhaps this is a clue if you are using this.

 


Comment by: Jan Karel Pieterse (4/10/2014 3:20:57 PM)

Hi Dave,

Ah, now I get it. The checkboxes are not meant to turn on or off a hotkey, but whether or not you need to use the control key in combination with the hotkey.

 


Comment by: Dave Hockin (4/10/2014 6:11:41 PM)

Hi Jan,

Surprized at your logic. With checkbox linked to Ctrl key it effectively means that user cannot type any of those letters and symbols unless they have box checked.

Eg deselecting checkbox for Check Formulas means I can't type Capital H in my spreadsheet without refTree launching into formula checking. ie can't type Total in a cell.Surely that is not what you had intended.

I can type into formula bar but not in the cell itself

 


Comment by: Jan Karel Pieterse (4/11/2014 11:45:08 AM)

Hi Dave,

Hmm, hadn't even considered that. Makes perfect sense now that you mention it :-)

 


Comment by: Jan Karel Pieterse (4/14/2014 6:47:06 AM)

Hi Dave,

On second thought, I do have a good reason for the control checkbox: you can also assign function keys (F1-F12) as s hort-cut keys and for those is makes sense not necessarily to have the control key pressed.

 


Comment by: Steven Katirai (5/5/2014 1:23:08 PM)

I have a model 70Mb big with over 500 sheets, will your package work with a file this size/

 


Comment by: Rene (5/8/2014 6:50:06 AM)

Hi, Jan Karel,

since one of the major updates (already few months ago), I am having quite some problems especially with some hot keys.

I am using Version 2.0 Build 54 (Excel 2007 12.0.6683,5002 SP3 MSO).

- Help File in Excel for the tool does not work for me. I can call it up and see the index but cannot enter any of the actual entries. Any ideas?
- Hot Keys: For me Shift+H seems to be a hot key selecting and checking some column (calls up a window "Found formulas in a range" if I select a non-empty cell). There seems to be no short key that can be disabled in the setting. And using Shift+H for a key would be rather unusual as I cannot type anything in Excel starting with a capital H unless I turn off the add-in.

Happy if you can have a look (and imagine) what I may be missing.

Thanks

 


Comment by: Jan Karel Pieterse (5/8/2014 7:10:41 AM)

Hi Rene,

I suspect you need to check the checkbox directly after "Check Formulas" (Settings screen). If you want to have no hotkey for an option, leave the textbox next to the checkbox empty, don't clear the checkbox as that is intended to indicate whether or not to use control plus the hotkey in the textbox.

 


Comment by: Jan Karel Pieterse (5/8/2014 7:21:25 AM)

Hi Steven,

It will. Some options in it will take a considerable amount of time though! Especially the Objects part, certainly if your sheets contain lots of objects, conditional formatting and Validation.

 


Comment by: John (6/5/2014 10:04:33 AM)

Morning Jan,

I have a quick question about licensing- Can I move licenses between machines? I'm interested in purchasing this but I know I'll be changing jobs soon so I'm unsure if the license can be changed at that time

 


Comment by: Jan Karel Pieterse (6/5/2014 10:23:57 AM)

Hi John,

Sure, you can use your license on any machine you like!

 


Comment by: RG (7/28/2014 3:27:31 PM)

Jan-Karel,

I still struggle with the shortcuts.

It seems to block a number of common key / shortcuts or other ones that I have already defined which I find quite inconvenient and I cannot seem to disable it?

1) Whether I check or uncheck the Ctrl+# shortcut in the settings, either the #-key or the Ctrl+#-key (for standard date formatting) is blocked.

2) Whether I check or uncheck the Ctrl+O shortcut in the settings, I cannot use the cominbation of Ctrl+Shift+O which seems counterintuitive. I have a shortcut assigned to that combination.

Can you take a look at this and advice a way around this?

On 2) I could assign a different shortcut of course (which I would avoid from pure habit).
On 1) I see currently no workaround at all.

Regards

 


Comment by: Jan Karel Pieterse (8/12/2014 10:53:30 AM)

Hi RG,

The checkboxes are a bit confusing: They only control whether or not the hotkey includes holding down the control key. To disable a hotkey, remove it textbox. So to make RefTreeAnalyser stop using the #, remove the # character.

 


Comment by: Marko (8/15/2014 2:53:31 PM)

Hi, Thanks for the lovely formula auditing tool! I'm evaluating version 2.0 Build 055, unfortunately I run very often to a VBA error message when I try to trace precedents:

Run-time error '-2147221503 (80040001)': Duplicate key: 'Precendents'Sheet1'!1681b'[myWorkbook.xlsm]Sheet2'U314bRoot'

I haven't discovered any clear pattern when this error message appears, but it always happens with same cells and about half of the formulas in my workbook. I have Excel 2013.

Do you know what could cause this? Thanks!

 


Comment by: Jan Karel Pieterse (8/15/2014 3:59:52 PM)

Hi Marco,

That error is likely caused by you probably having "Break in class modules" set in your VBA editor's settings (Alt+F11 to the VBE, tools, options, General tab).

The default setting is "Break on unhandled errors".

 


Comment by: Bill McNair (8/22/2014 12:49:33 AM)

A great, great formula diagnostic tool. Thank you!

One issue, I am used to having RefTree menu choices come up in the Excel right click mouse menu (from older versions of RefTree). I have noticed that the current version sometimes has the Reftree menu choices in the Excel right click menu and sometimes not. This strange behavior happens on my Win764bit/Excel201332bit and my Win8.164bit/Excel201332bit computers.

Could you add the ability to control the RefTree menu add to the Excel right click menu in the RefTree setting menu and force a reset of the Excel right click menu tree so that RefTree is included if so set? Thanks.

 


Comment by: Jan Karel Pieterse (8/22/2014 8:41:09 AM)

Hi Bill,

Thanks for the compliments!

I expect the instances where you don't see the RefTree entry in your right-click menu your worksheet is in a different view. This is because there are more than one type of cell right-click menus in Excel, depending on the worksheet view (Normal and page layout view share one right-click menu and Page break preview has a different one).

I'll note this as something to fix in the next build.

 


Comment by: online excel training (10/7/2014 2:00:06 PM)

This is great. Thanks for putting this together for an easier understanding.

 


Comment by: John (10/8/2014 7:14:44 AM)

Hi Jan.
Discount not operating at present. Or am I not seeing something.
Many thanks.

 


Comment by: Jan Karel Pieterse (10/8/2014 10:25:39 AM)

Hi John,

Did you enter the coupon code? MVP2014
Also, I think the discount is processed when you go to the next step of the ordering process (so after entering amount and coupon code).

 


Comment by: John (10/8/2014 11:59:32 AM)

Thanks Jan.
All good.
All worked fine.
Many thanks.

 


Comment by: Mark (10/11/2014 4:41:33 PM)

Hi!

I was wondering if there is a way to trace the exact precendent cell, across multiple levels, ideally with one click, and which will work for a vlookup formula.

e.g. I have 3 sheets- Sheet3 links to Sheet 2 via vlookup, which links to sheet1.

I downloaded your program and tried tracing back from Sheet 3. It will only trace back to Sheet2, and not directly to the cell but rather, the entire vlookup table.

Thanks!

Regards
Mark

 


Comment by: Jesse (10/12/2014 11:56:46 PM)

Tried it in OSX (both files) and got:

"Compile error in hidden module: UFReferences"

 


Comment by: Jan Karel Pieterse (10/13/2014 10:33:02 AM)

@Mark:

No, there is no such option in the tool. I like the idea, but it is quite a difficult one to accomplish!

@Jesse: I'm sorry, the tool only works on WIndows Excel.

 


Comment by: Joe (10/18/2014 1:02:14 AM)

Is 50% discount still available?

 


Comment by: Jan Karel Pieterse (10/18/2014 11:42:48 AM)

Hi Joe,

I'm afraid not!

 


Comment by: Phil (11/5/2014 1:46:41 AM)

I see others have mentioned the issue with table lookup formulas (most commonly vlookup or hlookup).

Currently, a dependent is listed if it refers to a cell within the Table_Array parameter of a vlookup or hlookup formula. The dependent is shown even if the Row_Index or Column_Index number points to some other cell within the Table_Array. Presumably, it would be difficult to parse the formula to determine if the lookup would hit the active cell. As a work-around, I would be happy with an option to drop any dependents for which the cell formula is a table lookup.

Thanks

 


Comment by: Jan Karel Pieterse (11/10/2014 11:41:56 AM)

Hi Phil,

The thing is, RefTreeAnalyser parses the formulas to extract cell references. There is no intelligence there to do things which are specific to one type of function, such as lookup functions and OFFSET or INDIRECT. This is also the case for Excels built-in formula tracing tools.

If you need to know which part of a table is "used" by a VLOOKUP, perhaps it is a better idea to use INDEX and MATCH instead. Combining these two functions enable you to point directly at the column from which the item is to be retrieved, rather than the convoluted column index number VLOOKUP requires you to use (and is much harder to audit in my opinion).

 


Comment by: Phil Guerney (11/10/2014 12:54:03 PM)

Thanks Jan,

I agree. Ever since learning about INDEX-MATCH, I have used it for my own spreadsheets. Unfortunately, I have to audit other peoples' work and the use of the table lookup functions does make this difficult.

The one I have in front of me today has formulas like this:

=HLOOKUP(D$751,$D$2:$AH$745,455,FALSE)

which refers to a block of data 31 cols x 744 rows and I have to scroll to find the 455th row (in this case) to see the precedent value because RefTreeAnalyser only refers to $D$2:$AH$745 as the precedent.

If I select cell D456 and ask for dependents, I get a list of 28 cells or ranges which "depend" on this cell, although only 7 really do so.

Anyway, thanks for a great tool which does save me a huge amount of time for a lot of the tedious work involved in my work.

Phil


 


Comment by: Robert Ed (3/6/2015 12:03:45 PM)


Hi Phil,
Thanks for the great work.

Is there a possibility to highlight all the precedents which are identified at all levels?

This would be very useful, for example for cleaning workbooks of unused data, or for finding which are the critical input data.

Thanks
Robert

 


Comment by: Jan Karel Pieterse (3/6/2015 1:21:13 PM)

Hi Robert,

No, currently there is no such method. But I certainly like the idea!

 


Comment by: Dmitri (3/18/2015 10:14:33 PM)

Will it work with Excel for Mac?

 


Comment by: Jan Karel Pieterse (3/19/2015 11:54:21 AM)

Hi Dmitri,

I'm afraid not!

 


Comment by: Roel (4/28/2015 11:21:53 PM)

Hi Jan,

Have you considered adding a function that would be called something like "Report Cell Names" and that would simply create an alphabetical index of all cell names, and their respective tabs and locations? It would love to be able to include such an index for my clients, when I finish & deliver a spreadsheet to them.

 


Comment by: Jan Karel Pieterse (4/29/2015 7:37:22 AM)

Hi Roel,

Try my Name Manager, which has such a list button:
www.jkp-ads.com/officemarketplacenm-en.asp

 


Comment by: Ken (5/28/2015 8:04:55 AM)

Hi - thank you for developing this amazing addin.

One quick question - how do the hotkeys work once I am tracing dependents. eg "Report", "Edit Audit Cell" "Do Active Cell" all have letters underlined which implies that there is a hotkey but the obvious guess of pressing the letter does not work for me? Cheers, Ken

 


Comment by: Jan Karel Pieterse (5/28/2015 9:23:49 AM)

Hi Tom,

Like in any Windows program, these require the alt key to be held down.

 


Comment by: Roy (6/23/2015 10:17:56 PM)

Hi

I'm impressed, however suddenly some Excel sheets are crashing now. I want to try to reinstall the tool but first need to uninstall it.
However, there's is no uninstall option, not in the start menu nor in the configuration/software section.

how do I uninstall???

 


Comment by: Jan Karel Pieterse (6/24/2015 11:27:29 AM)

Hi Roy,

Sorry to hear you are experiencing problems. What crash do you get precisely?

Uninstall is easy, just go to File, Excel options, Add-ins tab and click the Go button and uncheck RTA in the list.

Tip: alt+t followed by alt+i gets you there immediately.

 


Comment by: Roy (6/24/2015 3:05:48 PM)

I already found the problem.
My File is on my NAS and it seems that your addin cannot work with that.
When copying the file to my local PC it seems to work fine again.

 


Comment by: Jan Karel Pieterse (6/24/2015 3:15:20 PM)

Hi Roy,

OK, thank you for letting me know.

 


Comment by: Roy (6/24/2015 6:05:57 PM)

Hoi Jan Karel,

Ik zie dat je ook uit Nederland komt. ik zag je naam ook op een ander forum. Daar had ik een probleem gepost maar niemand schijnt daar de oplossing voor te weten :)

 


Comment by: Vikesh Jain (6/29/2015 9:35:13 PM)

Hi

If I exit the trace precedent window by pressing Esc key then Excel is not in focus - i have to activate excel again (by either clicking into it or using alt-tab).

can you please fix it.

 


Comment by: Jan Karel Pieterse (6/30/2015 11:18:37 AM)

Hi Vikesh,

Which Excel version are you using and how many Excel files were open at that moment?

 


Comment by: Nassim (12/2/2015 9:24:22 AM)

Hi,

This tool save me a lot of time.
One question, what's the shortcut for Azerty keyboard ?
Thanks.

 


Comment by: Jan Karel Pieterse (12/2/2015 11:45:42 AM)

Hi Nassim,

Since I do not own such a keyboard this is hard to asnwer :-) But you can easily change the short-cut keys in the settings dialog.

 


Comment by: Soichi (12/15/2015 12:36:49 AM)

Hi, I wanted to use this software but had a problem when I tried to install it.

The error message says "Cannot run the macro 'rxreftreecustomUI_onLoad'. The macro may not be available in this workbook or all macros may be disabled."

Could you help me? I use Windows 10 and Excel 2016.

Thank you.

 


Comment by: Jan Karel Pieterse (12/15/2015 8:22:37 AM)

Hi Soichi,

Odd, this should not happen. Perhaps your Office needs a repair?

 


Comment by: Matt Thompson (3/18/2016 6:03:34 PM)

how do I uninstall? Thanks!

 


Comment by: Jan Karel Pieterse (3/18/2016 7:26:38 PM)

Hi Matt,

Uninstall is simple:

- File, Options, Addins tab, click "Go...", uncheck RefTreeAnalyser.

 


Comment by: thelemontree10 (5/22/2016 8:44:53 AM)

Hi,

When I open your RefTreeAnalyserXL.xlam for first time, a message shows "Excel found unreadable content in 'RefTreeAnalyserXL.xlam'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.". I click Yes and another message popup says "Cannot run the macro 'rxreftreecustomUI_onLoad'. The macro may not be available in this workbook or all macros may be disabled.". I have turn on macro. So I think the file ZIP have some problems.

Please fix it soon.

Best Regards,

 


Comment by: Jan Karel Pieterse (5/23/2016 10:22:04 AM)

Hi thelemontree10,

Can you please try again? It works just fine for me. WHat Excel version do you use?

 


Comment by: thelemontree10 (5/23/2016 3:07:23 PM)

I use excel 2013 sp1. I have try so many times but nothing changed. So sad :(

 


Comment by: Jan Karel Pieterse (5/23/2016 8:36:36 PM)

Hi thelemontree10,

Odd, it works just fine for me on that version. What does "About Excel" on the account tab display as the exact version number? Perhaps a repair of your Office installation will help?

 


Comment by: Jean-Pierre Degroote (7/31/2016 4:10:31 PM)

Hi Jan Karel,

Using build 98.

In a new workbook, type in A1: =B1+1

Report formulas. => No formulas on sheet.
Next drag A1 to A2 & report formulas.

You get: Sheet1 =B1+1 A1:A2

 


Comment by: Douglas Hirsch (8/1/2016 5:51:56 PM)

Is the current build fully operational with Excel 2016? I have an issue that every time I start Excel, the add-in doesn't automatically load and I need to un-check and re-check it in the add-in bar. Can you please advise?

Thanks,
Doug

 


Comment by: Jan Karel Pieterse (8/1/2016 8:56:15 PM)

Hi Doug,

This is caused by a recent Office update I'm afraid. MSFT is working on a solution.

 


Comment by: Jan Karel Pieterse (8/9/2016 9:23:56 AM)

Hi Jean-Pierre,

Thank you, I will look into this.

 


Comment by: Douglas Hirsch (8/12/2016 5:32:30 PM)

Each time I use the hot keys to launch the trace precedents function, the settings menu opens up and I have to close it. Is this because I am using the trial version of the add-in?

 


Comment by: Jan Karel Pieterse (8/14/2016 12:29:16 PM)

Hi Douglas,

Yes that is precisely what happens. Registering the tool will remove that behaviour.

 


Comment by: Sam (8/23/2016 7:55:19 PM)

Is the license a one-time fee or does it need to be renewed annually?
Thanks

 


Comment by: Jan Karel Pieterse (8/23/2016 11:18:45 PM)

Hi Sam,

It is a one-time fee. It entitles you to free updates until I release a new version (which is not planned for another year or so)

 


Comment by: Gary Clarke (9/1/2016 2:57:06 PM)

Hello
Is 64 bit Excel 2016 supported?
Seems strange that only 2010 & 2013 64 bit supported
Thanks
Gary

 


Comment by: Jan Karel Pieterse (9/1/2016 3:00:47 PM)

Hi Gary,

Yes, 64 bit Excel 2016 is supported. I'll update the content of this page.

Thank you.

 


Comment by: Gary Clarke (9/1/2016 3:39:27 PM)

Thanks for quick response.
One more question.
Does your tool trace dependencies between tables on different sheets (in same workbook).
I can't seem to get standard Excel formula auditor (the one with millions of horrible blue arrows) to work with tables, my workbooks are huge and 90% tables.
Gary

 


Comment by: Jan Karel Pieterse (9/1/2016 4:43:28 PM)

Hi Gary,

Precedents to tables are properly traced at level 1 as at that level my tool parses the formula.

Unfortunately, for the Dependents analysis I depend on Excel's built-in tools so those are not detected unfortunately.

 


Comment by: Gary Clarke (9/1/2016 5:10:13 PM)

OK Thanks for your honesty.
Shame, tracing dependents is much more useful to me than precedents so I can check whether ranges are safe to delete or not.
Seems to be a tough problem,I can't find a way to do this with with VBA either.
If you figure out a solution, please mail me and I will definitely buy
Only 1 level is fine
Thanks

 


Comment by: Jan Karel Pieterse (9/1/2016 6:07:59 PM)

Hi Gary,

Tracking down dependents to tables is very complicated (and probably therefore slow). I'm looking at it, but can't promise anything.
I'd suggest purchasing, if I succeed in cracking that nut it'll be a free (and automatic) update for license holders.

 


Comment by: George (11/8/2016 6:49:15 AM)

I just downloaded the demo version and it has been working great all day for tracing precedents across multiple sheets in a model. When I opened up the same workbook this evening it does not appear to be working. I will select a cell and hit "precedents". First the pop-up screen shows up asking for license info. Then I hit "close" and it runs through the tracing precendents process, but then either does not display anything, or displays the excel built in precedent tracing arrows. I am using excel 2010. Any ideas?

 


Comment by: George (11/8/2016 6:52:51 AM)

Hi,

I just wrote. I think I fixed it byu hitting the "Reset Forms" button in the settings page. Thanks!

George

 


Comment by: Dave (11/8/2016 10:59:29 PM)

Moving RefAnalyser to new PC. IT dept tells me I am due for replacement. I am a licenced user. Can I install on new PC with same licence number?

Thanks

 


Comment by: Jan Karel Pieterse (11/14/2016 9:16:42 AM)

Hi Drew,

Sure, go ahead!

 


Comment by: Shaun (12/1/2016 7:19:58 PM)

Does this product work on Excel for Mac?

 


Comment by: Jan Karel Pieterse (12/2/2016 10:44:36 AM)

Hi Shaun,

I'm afraid not.

 


Comment by: raffaele (12/15/2016 3:20:14 PM)

Hi, your addin is really good. The only missing feature, that would make it excellent is if it could handle links to ranges on other xls files.
Ciao

 


Comment by: Jan Karel Pieterse (12/15/2016 4:03:32 PM)

Hi raffaele,

If the linked file is open in Excel the tool will use it.

 


Comment by: Sky (2/27/2017 4:18:14 PM)

Hi,

The newest update seems to have introduced a bug for me where the userform is hidden when tracing precedents/dependent, (the functionality still works in terms of locating the cells). Using excel 2016 on windows 10.

 


Comment by: Jan Karel Pieterse (2/27/2017 5:20:08 PM)

Hi Sky,

Apologies that you are experiencing a problem.
Can you please provide me with a screenshot?

 


Comment by: Sky (2/27/2017 5:24:07 PM)

Hi Jan,

There's nothing to screenshot, the userform flashes briefly on the screen and disappears. Arrow keys still allow me to navigate, alt+l still 'closes' and alt+a still changes the active cell, but at no point is the UI displayed other than if I select 'settings' from the ribbon.

 


Comment by: Jan Karel Pieterse (2/27/2017 5:30:53 PM)

Hi Sky,

This may be due to a change of your display setup, for example if last time you were using an external monitor or a larger display resolution. To reset the form position, open the settings screen and click the reset forms button.

 


Comment by: Sky (2/27/2017 5:32:36 PM)

Hi Jan,

That's cracked it! Many thanks!

 


Comment by: Raffaele (3/30/2017 6:37:34 PM)

Hi, many compliments for the addin I'd find it very useful in the Precedents feature, to see the value of the precedents not as ".value" but as ".text". So for example, if a precedent has a value of 1253514,254, but is shown (through formatting) as 12,5 (millions), it would be much better to see it in the precedents window as 12,5 than 253514,254 (as it is today). Thanks!

 


Comment by: Jan Karel Pieterse (3/31/2017 8:19:10 PM)

Hi Raffaele,

Nice suggestion, thanks.

 


Comment by: John Hackwood (7/23/2017 6:08:22 AM)

Hey Jan Karel

I bought your RefTreeAnalyser and its a great tool and I use it when I have a doozy of a formula.

Lately I have been getting the msg upon opening Excel when it loads Add-ins that checking for updates as failed. I guess I could unclick the check for updates automatically but has the support for this add-in now stopped and that's the reason?

I could send you a screen shot if you want.

cheers
John

 


Comment by: Jan Karel Pieterse (7/23/2017 10:44:33 AM)

Hi John,

RefTreeAnalyser is still fully supported indeed and I'm working (slowly) on version 3.

So I guess the problem is on your end. Perhaps a setting in your firewall?

 


Comment by: John (7/23/2017 1:32:15 PM)

Hi again Jan Karel, OK will try that.

Sorry I didn't know this comment was going to be public and gave my license no. in the above comment, so please delete/redact it. thanks John

 


Comment by: Jan Karel Pieterse (7/23/2017 8:21:31 PM)

Hi John,

Done!

 


Comment by: Den (8/6/2017 12:14:54 AM)

Hi Jan,
there is an error in tracing when i use named table with name in cyrillic characters. If I use the named column with сyrillic, there is no error.

Some suggestions for next release:
1)In the interface, select the structure of the formula (not just each individual element) and display the calculated value for each block. If the formula uses a logical expression to calculate the result, display how the elements of the compared block match the condition (select suitable ones). If you use the array formula in the condition, display the matching array elements.
2) If the formula uses ranges / arrays of the same dimensions, display the array elements under each other. That is, it is necessary to align to the widest value of the element of the array / range group.
3) Make it possible to correct the formula via the add-in interface.
4) Connect the add-in to all Excel windows in which you entering the formulas:
a) Conditional formatting formula;
b) Data validation formula;
c) Formula for the named range in Name manager.
To check the operation of the formulas in these blocks, it is necessary to select the cell with which to calculate the formula. It is especially important when the formula uses an offset.

Thanks for the excellent add-on and further success!

 


Comment by: Jan Karel Pieterse (8/13/2017 5:14:35 PM)

Hi Den,

Thanks for leting me know!

 


Comment by: Alessio (8/24/2017 6:53:16 PM)

='\\Solvency II\2017\Quarterly\Q2 2017\Lite Model\Inputs Q117\Asset Inputs\[Convert_BalSheet_Items_to_ICM_Inputs_Q117HC_RCAP_V1.xlsm]1.RISK_DRIVERS'!E73

That's one of my link.
Your program stops at "Q2" because there is a space.
I need to open the workbook to fix this.
any other way around it?

Thanks,
Alessio

 


Comment by: Jan Karel Pieterse (8/25/2017 1:15:37 PM)

Hi Alessio,

Thanks!

 


Comment by: Alessio (8/31/2017 1:23:25 PM)

I know that sound cheeky but if you can send me an e-mail with a temporary licence, I can see if there are any bugs in the locked-up icons.

This is a very good VBA app.

 


Comment by: Jan Karel Pieterse (8/31/2017 2:30:30 PM)

Hi Alessio,

Erm, not sure what you mean by locked up icons?

 


Comment by: Kirran (9/19/2017 12:43:19 PM)

Hi Jan,

It would be great if you could include an option in settings not to unhide all hidden sheets. Sometimes the addin doesn't re-hide them and it's a bit of a pain when you know they aren't going to be used in the ref tree.

Many thanks,

Kirran

 


Comment by: Jan Karel Pieterse (9/20/2017 4:33:27 PM)

Hi Kirran,

Good suggestion!

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.