RefTreeAnalyser: The ultimate Excel Formula Auditing tool

For Microsoft^® Excel^®

Excel formula auditing taken to the next level!

Content

Features
Free Download
Ordering licenses
Money back guarantee
What is RefTreeAnalyser?
Supported Excel versions
More information

Features

Easy Auditing of formula dependents and precedents, including object dependencies (charts, pivot tables, form controls, Validation formulas, Conditional formatting formulas, etcetera)
Find circular references (Excel for Windows only!)
Trace errors
Find out which cells are used by all kinds of things in your file: pivot tables, charts, conditional formatting, data validation, forms and ActiveX controls and etcetera.
Generate a table of content containing not just your worksheets, but also your charts, pivot tables and (form) controls
Time your workbook calculation for each worksheet to find bottlenecks
Check columns for formula inconsistencies

Free Download

Supported Excel versions:

Windows versions: Microsoft 365, 2021, 2019, 2016, 2013, 2010, and 2007
Mac versions: Microsoft 365 (please note that the circular reference detection does not work on Mac Excel)
Supports both 32 and 64 bit Office

Download RefTreeAnalyser (Free demo!!!) V3.1 (Build 199, Updated May 16, 2023, downloaded: 22.431 times)

After downloading the file, please read the readme.txt file, it contains important installation instructions. In addition, please read: Excel add-ins fail to load

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.

Old versions

If you need an old version of RefTreeAnalyser, please contact me.

Ordering licenses

A license includes:

Installation and perpetual use of the tool on two computers
One year of free updates
Permanent licenses are available upon request

Prices

Amount

Price per license

1-10

€ 35.00

11-50

€ 32.00

51-100

€ 29.00

101-300

€ 26.50

301+

€ 24.00

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.

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!

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

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 2007 and up, including all 64 bit Excel versions.

More information

Comments

All comments about this page:

Comment by: Doug Glancy (17-12-2007 13:49:47) deeplink to this comment

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 (17-12-2007 22:24:07) deeplink to this comment

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 (25-2-2008 15:26:25) deeplink to this comment

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

Comment by: Kevin H. Stecyk (7-4-2008 06:24:23) deeplink to this comment

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 (8-4-2008 00:53:21) deeplink to this comment

Hi Kevin,

Thanks!! Much appreciated.

Comment by: John Russell (4-7-2008 20:34:07) deeplink to this comment

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 (22-9-2008 16:47:46) deeplink to this comment

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 (23-9-2008 01:25:00) deeplink to this comment

Hi Jeff,

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

Comment by: Norm (26-5-2010 07:46:38) deeplink to this comment

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 (26-5-2010 08:09:52) deeplink to this comment

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 (26-7-2010 09:21:21) deeplink to this comment

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 (16-8-2010 03:10:05) deeplink to this comment

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 (23-8-2010 00:45:35) deeplink to this comment

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 (23-8-2010 03:53:06) deeplink to this comment

Hi Hikhil,

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

Comment by: William McNair (19-11-2010 21:36:44) deeplink to this comment

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 (21-11-2010 22:21:44) deeplink to this comment

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 (18-1-2011 02:43:35) deeplink to this comment

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 (18-1-2011 03:45:29) deeplink to this comment

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 (3-5-2011 17:25:08) deeplink to this comment

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 (4-5-2011 00:00:28) deeplink to this comment

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 (28-10-2011 11:36:12) deeplink to this comment

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 (31-10-2011 01:09:13) deeplink to this comment

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 (4-11-2011 02:33:11) deeplink to this comment

Does it work on Macintosh ?

Comment by: Jan Karel Pieterse (4-11-2011 05:05:53) deeplink to this comment

Hi Bernard,

Unfortunately not!

Comment by: yves labidoire (31-1-2012 00:13:10) deeplink to this comment

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 (31-1-2012 06:06:47) deeplink to this comment

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 (9-2-2012 18:26:59) deeplink to this comment

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 (10-2-2012 00:57:23) deeplink to this comment

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 (17-4-2012 13:40:27) deeplink to this comment

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 (17-4-2012 21:20:33) deeplink to this comment

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 (27-4-2012 10:36:16) deeplink to this comment

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 (26-2-2013 09:18:27) deeplink to this comment

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

Comment by: Jan Karel Pieterse (26-2-2013 11:14:38) deeplink to this comment

Hi Jason,

Not yet, but I am working on one.

Comment by: Jason (3-5-2013 01:57:15) deeplink to this comment

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 (3-5-2013 11:29:23) deeplink to this comment

Hi Jason,

Thanks for the compliments, much appreciated.

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

Comment by: Jason (4-5-2013 01:39:04) deeplink to this comment

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 (4-5-2013 10:45:32) deeplink to this comment

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 22:20:21) deeplink to this comment

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 (6-5-2013 08:35:26) deeplink to this comment

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 (6-5-2013 09:49:11) deeplink to this comment

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 (7-5-2013 22:07:30) deeplink to this comment

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 (8-5-2013 10:28:17) deeplink to this comment

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 (27-5-2013 11:32:25) deeplink to this comment

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 (28-5-2013 08:51:19) deeplink to this comment

Hi Craig,

Can you please try build 019?

Comment by: Rene (3-6-2013 12:32:42) deeplink to this comment

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 (3-6-2013 16:06:25) deeplink to this comment

Hi Rene,

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

Comment by: Rene (3-6-2013 16:47:28) deeplink to this comment

No more probs.

Cheers, man.

Comment by: David (12-8-2013 23:51:07) deeplink to this comment

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

Comment by: Jan Karel Pieterse (13-8-2013 07:34:50) deeplink to this comment

Hi David,

Apologies, bug fixed.

Comment by: Dave Hockin (29-10-2013 13:04:29) deeplink to this comment

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 (29-10-2013 16:23:40) deeplink to this comment

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 (4-11-2013 11:42:55) deeplink to this comment

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 (4-11-2013 12:37:10) deeplink to this comment

Hi Wouter,

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

Comment by: Dave Hockin (7-11-2013 20:26:15) deeplink to this comment

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 (8-11-2013 19:39:44) deeplink to this comment

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 (21-11-2013 11:05:52) deeplink to this comment

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 (21-11-2013 13:53:56) deeplink to this comment

Hi Kanwaljit,

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

Comment by: Eileen (4-12-2013 19:06:10) deeplink to this comment

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 (5-12-2013 07:48:16) deeplink to this comment

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 (9-1-2014 19:15:37) deeplink to this comment

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 (10-1-2014 11:14:26) deeplink to this comment

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 (10-4-2014 01:40:09) deeplink to this comment

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 (10-4-2014 09:54:08) deeplink to this comment

Hi Dave,

What if you change it again and then restart Excel?

Comment by: Dave Hockin (10-4-2014 14:22:56) deeplink to this comment

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 (10-4-2014 15:20:57) deeplink to this comment

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 (10-4-2014 18:11:41) deeplink to this comment

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 (11-4-2014 11:45:08) deeplink to this comment

Hi Dave,

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

Comment by: Jan Karel Pieterse (14-4-2014 06:47:06) deeplink to this comment

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 13:23:08) deeplink to this comment

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

Comment by: Rene (8-5-2014 06:50:06) deeplink to this comment

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 (8-5-2014 07:10:41) deeplink to this comment

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 (8-5-2014 07:21:25) deeplink to this comment

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 (5-6-2014 10:04:33) deeplink to this comment

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 (5-6-2014 10:23:57) deeplink to this comment

Hi John,

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

Comment by: RG (28-7-2014 15:27:31) deeplink to this comment

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 (12-8-2014 10:53:30) deeplink to this comment

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 (15-8-2014 14:53:31) deeplink to this comment

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 (15-8-2014 15:59:52) deeplink to this comment

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 (22-8-2014 00:49:33) deeplink to this comment

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 (22-8-2014 08:41:09) deeplink to this comment

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 (7-10-2014 14:00:06) deeplink to this comment

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

Comment by: John (8-10-2014 07:14:44) deeplink to this comment

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

Comment by: Jan Karel Pieterse (8-10-2014 10:25:39) deeplink to this comment

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 (8-10-2014 11:59:32) deeplink to this comment

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

Comment by: Mark (11-10-2014 16:41:33) deeplink to this comment

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 (12-10-2014 23:56:46) deeplink to this comment

Tried it in OSX (both files) and got:

"Compile error in hidden module: UFReferences"

Comment by: Jan Karel Pieterse (13-10-2014 10:33:02) deeplink to this comment

@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 (18-10-2014 01:02:14) deeplink to this comment

Is 50% discount still available?

Comment by: Jan Karel Pieterse (18-10-2014 11:42:48) deeplink to this comment

Hi Joe,

I'm afraid not!

Comment by: Phil (5-11-2014 01:46:41) deeplink to this comment

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 (10-11-2014 11:41:56) deeplink to this comment

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 (10-11-2014 12:54:03) deeplink to this comment

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 (6-3-2015 12:03:45) deeplink to this comment

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 (6-3-2015 13:21:13) deeplink to this comment

Hi Robert,

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

Comment by: Dmitri (18-3-2015 22:14:33) deeplink to this comment

Will it work with Excel for Mac?

Comment by: Jan Karel Pieterse (19-3-2015 11:54:21) deeplink to this comment

Hi Dmitri,

I'm afraid not!

Comment by: Roel (28-4-2015 23:21:53) deeplink to this comment

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 (29-4-2015 07:37:22) deeplink to this comment

Hi Roel,

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

Comment by: Ken (28-5-2015 08:04:55) deeplink to this comment

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 (28-5-2015 09:23:49) deeplink to this comment

Hi Tom,

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

Comment by: Roy (23-6-2015 22:17:56) deeplink to this comment

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 (24-6-2015 11:27:29) deeplink to this comment

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 (24-6-2015 15:05:48) deeplink to this comment

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 (24-6-2015 15:15:20) deeplink to this comment

Hi Roy,

OK, thank you for letting me know.

Comment by: Roy (24-6-2015 18:05:57) deeplink to this comment

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 (29-6-2015 21:35:13) deeplink to this comment

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 (30-6-2015 11:18:37) deeplink to this comment

Hi Vikesh,

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

Comment by: Nassim (2-12-2015 09:24:22) deeplink to this comment

Hi,

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

Comment by: Jan Karel Pieterse (2-12-2015 11:45:42) deeplink to this comment

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 (15-12-2015 00:36:49) deeplink to this comment

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 (15-12-2015 08:22:37) deeplink to this comment

Hi Soichi,

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

Comment by: Matt Thompson (18-3-2016 18:03:34) deeplink to this comment

how do I uninstall? Thanks!

Comment by: Jan Karel Pieterse (18-3-2016 19:26:38) deeplink to this comment

Hi Matt,

Uninstall is simple:

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

Comment by: thelemontree10 (22-5-2016 08:44:53) deeplink to this comment

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 (23-5-2016 10:22:04) deeplink to this comment

Hi thelemontree10,

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

Comment by: thelemontree10 (23-5-2016 15:07:23) deeplink to this comment

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

Comment by: Jan Karel Pieterse (23-5-2016 20:36:36) deeplink to this comment

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 (31-7-2016 16:10:31) deeplink to this comment

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 (1-8-2016 17:51:56) deeplink to this comment

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 (1-8-2016 20:56:15) deeplink to this comment

Hi Doug,

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

Comment by: Jan Karel Pieterse (9-8-2016 09:23:56) deeplink to this comment

Hi Jean-Pierre,

Thank you, I will look into this.

Comment by: Douglas Hirsch (12-8-2016 17:32:30) deeplink to this comment

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 (14-8-2016 12:29:16) deeplink to this comment

Hi Douglas,

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

Comment by: Sam (23-8-2016 19:55:19) deeplink to this comment

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

Comment by: Jan Karel Pieterse (23-8-2016 23:18:45) deeplink to this comment

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 (1-9-2016 14:57:06) deeplink to this comment

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

Comment by: Jan Karel Pieterse (1-9-2016 15:00:47) deeplink to this comment

Hi Gary,

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

Thank you.

Comment by: Gary Clarke (1-9-2016 15:39:27) deeplink to this comment

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 (1-9-2016 16:43:28) deeplink to this comment

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 (1-9-2016 17:10:13) deeplink to this comment

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 (1-9-2016 18:07:59) deeplink to this comment

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 (8-11-2016 06:49:15) deeplink to this comment

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 (8-11-2016 06:52:51) deeplink to this comment

Hi,

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

George

Comment by: Dave (8-11-2016 22:59:29) deeplink to this comment

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 (14-11-2016 09:16:42) deeplink to this comment

Hi Drew,

Sure, go ahead!

Comment by: Shaun (1-12-2016 19:19:58) deeplink to this comment

Does this product work on Excel for Mac?

Comment by: Jan Karel Pieterse (2-12-2016 10:44:36) deeplink to this comment

Hi Shaun,

I'm afraid not.

Comment by: raffaele (15-12-2016 15:20:14) deeplink to this comment

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 (15-12-2016 16:03:32) deeplink to this comment

Hi raffaele,

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

Comment by: Sky (27-2-2017 16:18:14) deeplink to this comment

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 (27-2-2017 17:20:08) deeplink to this comment

Hi Sky,

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

Comment by: Sky (27-2-2017 17:24:07) deeplink to this comment

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 (27-2-2017 17:30:53) deeplink to this comment

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 (27-2-2017 17:32:36) deeplink to this comment

Hi Jan,

That's cracked it! Many thanks!

Comment by: Raffaele (30-3-2017 18:37:34) deeplink to this comment

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 (31-3-2017 20:19:10) deeplink to this comment

Hi Raffaele,

Nice suggestion, thanks.

Comment by: John Hackwood (23-7-2017 06:08:22) deeplink to this comment

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 (23-7-2017 10:44:33) deeplink to this comment

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 (23-7-2017 13:32:15) deeplink to this comment

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 (23-7-2017 20:21:31) deeplink to this comment

Hi John,

Done!

Comment by: Den (6-8-2017 00:14:54) deeplink to this comment

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 (13-8-2017 17:14:35) deeplink to this comment

Hi Den,

Thanks for leting me know!

Comment by: Alessio (24-8-2017 18:53:16) deeplink to this comment

='\\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 (25-8-2017 13:15:37) deeplink to this comment

Hi Alessio,

Thanks!

Comment by: Alessio (31-8-2017 13:23:25) deeplink to this comment

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 (31-8-2017 14:30:30) deeplink to this comment

Hi Alessio,

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

Comment by: Kirran (19-9-2017 12:43:19) deeplink to this comment

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 (20-9-2017 16:33:27) deeplink to this comment

Hi Kirran,

Good suggestion!

Comment by: Marvin Rivas (15-12-2017 20:15:04) deeplink to this comment

I recently upgraded to using excel 2016 and now that I try to relaunch the reftreelauncher it does not install. Can you help as how I can fix this problem?

Thank you,
Marvin

Comment by: Jan Karel Pieterse (18-12-2017 10:01:04) deeplink to this comment

Hi Marvin,

It seems some recent updates are preventing RTA from working. I am looking at how I can resolve these issues.

Comment by: Lois (15-1-2018 03:34:26) deeplink to this comment

Hi!

Can I ask what's an alternative for Spreadsheet Studio for excel?

Thanks.

Comment by: Jan Karel Pieterse (15-1-2018 07:58:18) deeplink to this comment

Hi Lois,

Spreadsheet studio is a great tool and in some ways competes with my RefTreeAnalyser. Some RTA benefits:

- Easier navigation of formula dependents and precedents
- Visualisation of dependents
- Only tool available which is capable of showing al circular references in a file
- The only tool that also includes object references such as pivot table sources, chart source data, conditional formatting, data validation, form controls, etcetera.

Comment by: Ruben (16-1-2018 17:16:30) deeplink to this comment

Love the add-in. Since I only use my keyboard with excel, I wonder if there is a (or if I could make one myself) for the close button in the tracing precedents menu.

Comment by: Jan Karel Pieterse (16-1-2018 17:29:17) deeplink to this comment

Hi Ruben,

Doesn't pressing Escape work for you?

Comment by: Ruben (16-1-2018 17:32:30) deeplink to this comment

Hello Jan Karel,

Thank you for the quick reply. Escape works, but then I jump to the original cell, instead of the cell that is currently being selected. This is sometimes a hassle for me, since these might be on entirely different sheets.

Comment by: Jan Karel Pieterse (16-1-2018 17:52:30) deeplink to this comment

Hi Ruben,

I get it. The short-cut key for the close button is alt+l (small L)

Comment by: Ruben (16-1-2018 19:17:07) deeplink to this comment

Thx! Appreciate it!

Comment by: Pierre Maitre (4-2-2018 03:50:14) deeplink to this comment

Jan,

I just purchased and installed your addin. The reason for buying your software is that I have created some circular in my spreadsheet and I cannot find them.

I am using Excel 2016.

How does your addin work with circular reference? do you scan the spreadsheet? do you capture the excel error message ?

Right now, nothing happens. I am getting the Excel circular message just as I did before.

Please, help. I am not an excel expert.

Thanks, Pierre

Comment by: Jan Karel Pieterse (4-2-2018 12:12:27) deeplink to this comment

Hi Pierre,

In the ribbon tab called RefTreeAnalyser there is a dedicated button to search for the circular chains of cells. The next screen you get is there to explain that RTA will make tempoary changestonyour file, so it gives you an opportunity to choose whether it should save a backup copy first. RTA then lists all chains it finds so you can decide what to do with them.
If you need help, send me an email (address at bottom of this page).

Comment by: Dave (2-3-2018 21:26:25) deeplink to this comment

Hi Jan, Long time user, just found what might be a bug or a miss on the precedents trace.
Version 2.0 Build 124 Registered user

Sample formula : =CCA!J314+SUM(CapEx!$J$702:CapEx!J702)
Formula is on my "Taxes" sheet.
Precedent reported for CCA cell but not for the sum range on CapEx sheet.

Excel's formula precedent will report this as 3 precedents and RTA reports 1 precedent. Is this a limitation of your ability in RTA to trace or is there a fixable bug ?

Comment by: Jan Karel Pieterse (5-3-2018 10:18:03) deeplink to this comment

Hi Dave,

That is indeed a bug, but your syntax is rather unusual. If you change the formula to this RTA will pick it up as expected:

=CCA!J314+SUM(Capex!$J$702:J702)

Comment by: Kirran Sky (9-3-2018 15:24:34) deeplink to this comment

Hi Jan,

Any chance you can update to pick up structured references?

e.g. =Table_Name[[#Totals],[Jan-18]] throws out an error.

Comment by: Jan Karel Pieterse (9-3-2018 15:57:52) deeplink to this comment

Hi Kirran,

It should do so, yours appears an exception :-(

Comment by: Rene (13-5-2018 17:04:23) deeplink to this comment

Hi Jan,

I all of a sudden started to receive a "Run-time error '13': Type mismatch.

The RefTreeAnalyser still appears on the Ribbon but I cannot use its functionality anymore.

Help button or "Time Selected Cells" still works but most other buttons result in the above error message and upon clicking Debug Excel crashes.
I already tried deinstalling and virtually all security, macros settings etc. are set to lowest level in Excel.

Thanks

Comment by: Jan Karel Pieterse (14-5-2018 07:25:32) deeplink to this comment

Hi Rene,

Which version are you using?

Comment by: Rene (14-5-2018 10:53:45) deeplink to this comment

Hi Jan,

RefTreeAnalyzer Version 2.0 Build 125, Release 2018-05-02.
I am using Office 2010.

The add-in works fine on different computers that I tried, so I might have messed something up in Excel that I cannot see yet.

E.g. I switched recently the language pack but switching back didn't change anything either.

I think it is somehow related to the Ribbon. When I tried to disable all other add-ins, the Add-In Ribbon pane with some add-ins in it remained and I couldn't delete them. They were neither found in the Add-In folder (the one through the Excel / Tools/ Options / Browse function) nor could I get rid of them by deleting my entire Excel customization, i.e. by deleting in the registry the "Options" folder under: HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel.
Deleting the entire 'Excel' folder didn't help either.

I then deleted the entries on the Add-In Ribbon pane manually with a right, but still no success.

So I've run out of options now what to try to get it back to work (other than completely resetting / repairing Office).
(The add-in actually did work on my computer a few days ago.)
Any suggestions welcome.

Thanks

Comment by: Jan Karel Pieterse (14-5-2018 11:03:36) deeplink to this comment

Hi Rene,

Download the latest version, I expect the issue will be resolved.

Comment by: Shilpa (13-6-2018 11:37:58) deeplink to this comment

Hello All,
Can you please confirm the RefTreeAnalyser license is an subscription or perpetual license.

Comment by: Jan Karel Pieterse (13-6-2018 11:40:26) deeplink to this comment

Hi Shilpa,

It is perpetual, but it entitles you to one year of updates. If your year has expired the tool will work but no longer receive updates.

Comment by: Thomas Plagwitz (18-6-2018 23:46:17) deeplink to this comment

Hi JKP:

I have been a happy customer of RTA for a while, but now need to automate running it over a large number of Excel files.

However, I found that whatever I dim the control parameter as:

Dim oControl as Object ' Variant ' iRibboncontrol

the following line will crash my Excel (2010) :

Application.Run "'reftreeanalyserxl.xlam'!rxreftreeFormulaCheckAllSheets", oControl

Any pointers?

TIA,
Tom

Comment by: Thomas Plagwitz (19-6-2018 06:23:27) deeplink to this comment

Some additional testing information for Application.running RTA methods:

Excel 2016 won't crash, but returns "Unexpected error (40182)", no matter what datatype oControl is declared as.

Also tests the same with another RTA procedure:

Application.Run "'reftreeanalyserxl.xlam'!rxreftreeFormulaCheckAllSheets", oControl

I am trying to follow Ron de Bruin's Application.Run methodology here. Not using oControl just gives 449 "argument not optional", but at least my base syntax seems correct.

Comment by: Jan Karel Pieterse (19-6-2018 10:29:57) deeplink to this comment

Hi Thomas,

You can invoke that report using:

application.Run "'reftreeanalyserxl.xlam'!jkpRefTreeGenerateFormulaReport"

Comment by: Thomas Plagwitz (25-6-2018 04:59:51) deeplink to this comment

It works great, thanks! Using this, I managed to analyse formulas for about a 1000 workbooks from a pipeline.

Is it possible, however, to turn off the alert "Timing formula calculation seems to take long, skip for worksheet", either default to yes or even skip timing for all worksheets? I have not found a setting in the PDF documentation, nor another way to suppress or OK this dialog.

Also, could you share the internal equivalents for the 2 ribbon commands rxreftreeListAllSheetStats and rxrtFormulaCheckSheetsRefs.

Thanks again, tom

Comment by: Gareth Morgan (16-10-2018 18:47:15) deeplink to this comment

If my updates year has expired do I have to buy the whole thing again?

Comment by: Jan Karel Pieterse (17-10-2018 09:54:33) deeplink to this comment

Hi Gareth,

Well, yes and no.

No: your license stays valid, it is just the updating which expires.

Yes: If you want another year of updates you need a new license.

Comment by: John Busbee (20-10-2018 16:44:18) deeplink to this comment

Does this work with Excel 2016 for mac? It gave me a visual Basic compile error when I clicked on the add-in file and said something about compatible versions.

Comment by: Jan Karel Pieterse (22-10-2018 10:10:57) deeplink to this comment

Hi John,

I'm afraid there is no Mac supported version.

Comment by: Teodor (13-12-2018 13:58:05) deeplink to this comment

Hello Jan,

Is the newest plug in compatible with Office 2019.
After installing 2019 on one of my client's machines the plug in didn't want to run.
Thank you very much for your help.

Kind Regards,

Teodor Rangelov

Comment by: Jan Karel Pieterse (13-12-2018 14:21:49) deeplink to this comment

Hi Teodoor,

Yes it should work. Have you unlocked the add-in as shown on this page:
https://www.jkp-ads.com/Articles/Excel-Add-ins-fail-to-load.asp

Comment by: Jamiejes (6-1-2019 23:32:28) deeplink to this comment

Hey. I sent a screenshot. Did you get it?

Comment by: Jan Karel Pieterse (2-2-2019 16:25:57) deeplink to this comment

Hi Jamie,

I don't think I have.

Comment by: Allie (1-5-2019 21:25:00) deeplink to this comment

Hi there - I have a laptop that I plug into a monitor to use both screens. However, when I am only on the laptop and I initiate the Ref Tree Analyser, I can't see the dialogue box on my laptop. Is it searching for the other screen? Seems weird since all other applications know I'm not plugged in.

Secondly, every time I launch the Ref Tree Analyser it unhides all my hidden tabs. This is getting annoying as I need to re-hide the tabs once I'm done. The version I had before this didn't have that problem.

Please let me know how to fix both these issues. Thanks!

Comment by: Jan Karel Pieterse (2-5-2019 10:49:00) deeplink to this comment

Hi Allie,

That indeed is the case. You can reset the position by opening the settings screen and pressing the "Reset Forms" button.

I agree this needs some work, but I haven't yet found a reliable way to detect whether the screen the dialog is shown on is actually attached to the system :-(

Comment by: Allie (2-5-2019 18:06:00) deeplink to this comment

Thanks so much for the quick response! I'll hit the Reset Forms next time.

Do you know how to fix it so that the application does not unhide hidden tabs? Thanks so much! This tool is amazing - I wouldn't be as efficient without it.

Comment by: Jan Karel Pieterse (3-5-2019 11:31:00) deeplink to this comment

Hi Allie,

Thanks, glad you like it.

Does unchecking the "Unhide sheets" checkbox on the settings dialog work for you?

Comment by: Allie (3-5-2019 18:25:00) deeplink to this comment

So simple - I should have seen that dialog box. Thanks!!

Comment by: David (9-10-2019 03:25:00) deeplink to this comment

Can one license key be assigned to multiple licenses?

If we purchase additional licenses, can the same license key be used for those new licenses?

Can we allocate/deallocate users to licenses from your web site?

Comment by: Jan Karel Pieterse (9-10-2019 10:50:00) deeplink to this comment

Hi David,

Yes, you can use a single key for multiple licenses. There is no means to deallocate registrations, but as long as the number of registrations using the license key stays within reason there is no need to do so.

I do prefer that you use the new license code if you purchase additional licenses.

Comment by: john (4-1-2020 10:42:00) deeplink to this comment

The addin is perfect but can you add evaluate formula option(like in excel) in this addin

Comment by: Jan Karel Pieterse (6-1-2020 11:49:00) deeplink to this comment

Hi John,

Thank you for the suggestion!

Comment by: Alejandro (14-7-2020 22:14:00) deeplink to this comment

Hi, this license this sale in chile?, exist one representative in chile for companies?

Comment by: Jan Karel Pieterse (18-7-2020 19:10:00) deeplink to this comment

Hi Alejandro,

You can buy the license directly from this website.

Comment by: Gearoid (1-10-2020 16:01:00) deeplink to this comment

Hi there,

Does this work with Excel 365. Can I trial it before I buy it?

Thanks

Comment by: Jan Karel Pieterse (2-10-2020 16:28:00) deeplink to this comment

Hi Gearoid,

Yes indeed it works with any Excel version (for Windows!) as of Excel 2007 up to and including Excel 365.
You can download the tool and try it as it automatically works in demo mode with limited functionality.

Once you're happy with the tool you can go back to this page, purchase a license (sent to you by email).

You can enter the license code into the settings dialog of the tool and it will unlock into the full version automatically.

Comment by: Matías Pavez (8-4-2021 20:51:00) deeplink to this comment

I have a Problem with the precedents function.
It doesnt work in this last update!

Comment by: Jan Karel Pieterse (9-4-2021 11:51:00) deeplink to this comment

Hi Matias,

I'm sorry to hear you are having an issue. Does it help to open the settings screen and click the Rest Forms button?

Comment by: Alejandro Santiago (29-6-2021 17:33:00) deeplink to this comment

I bought the license a few years ago.
It throws an error checking for updates.
Am I not able to keep using the addon unless I repurchase the license?

Comment by: Jan Karel Pieterse (30-6-2021 10:49:00) deeplink to this comment

Hi Alejandro,

No, the license is still valid. Just turn off automatic updates in Settings.

If you want to use your license on a new machine, make sure you copy the reftreeanalyserxl.xlam file from your old machine, as downloading a new version from my website will not work.

Comment by: Axel (16-2-2022 14:27:00) deeplink to this comment

Hi, can you confirm whether this is working with the latest version of excel? When I double click the refutility, it doesnt bring up any windows (just switches to the first open excel window).

Comment by: Jan Karel Pieterse (16-2-2022 14:55:00) deeplink to this comment

Hi Axel,

It should work, yes. It does for me (I use Microsoft 365 monthly channel).
Perhaps you are experiencing this issue: https://jkp-ads.com/Articles/Excel-Add-ins-fail-to-load.asp

Comment by: Kanwaljit Singh Dhunna (3-7-2022 20:04:00) deeplink to this comment

Hello Sir,
Greetings of the Day !
Does the Reftreeanalyser works with LAMBDA and Dynamic Array Formulas or not ?

Kanwaljit

Comment by: Jan Karel Pieterse (3-7-2022 22:24:00) deeplink to this comment

Hi Kanwaljit,

Yes, RefTreeAnalyser recognizes dynamic arrays and lambda functions.

Comment by: wiensha (1-10-2022 09:44:00) deeplink to this comment

Change

Download RefTreeAnalyser (Free demo!!!) V3.0 (Build 187, Updated September 22, 2022, downloaded: 21.226 times)

Into

Download RefTreeAnalyser (Free demo!!!) V3.1 (Build 187, Updated September 22, 2022, downloaded: 21.226 times)

(V3.0 should be V3.1 on this page)

Comment by: Jan Karel Pieterse (3-10-2022 09:48:00) deeplink to this comment

Well spotted Wiens, thanks.

Comment by: Adrien (3-11-2022 16:16:00) deeplink to this comment

Hi Jan,

thanks for your job. Here some ideas :
it would be great if the formulas analyser window could present the ( ) in differents colors to read easier long formulas. An alternative solution could be to cut the formulas with line breaks as VBA do.

Regards,

Comment by: Jan Karel Pieterse (3-11-2022 17:16:00) deeplink to this comment

Hi Adrien,

Thank you for your ideas, much appreciated!

Comment by: Mohamed GadAllah (22-11-2022 21:10:00) deeplink to this comment

When I open several workbooks, and switch between them, I've got an error:
Failed To Register Hotkey For Visualize.
https://imgur.com/n1qKvsA

Comment by: Jan Karel Pieterse (23-11-2022 12:01:00) deeplink to this comment

Hi Mohamed,

If you open RefTreeAnalyser Settings, could you please tell me which hotkey currently is set for the Visualize option?

Perhaps if you try a different one (or blank the key and uncheck the control box) it will stop showing the message?

Comment by: Mohamed GadAllah (26-11-2022 11:11:00) deeplink to this comment

Thanks for replying to me.
https://imgur.com/a/0toSYYA

Comment by: Mohamed GadAllah (26-11-2022 11:14:00) deeplink to this comment

If the info may affect your solution.
Any Excel add-in file I used to placed it here:
C:\Users\UserName\AppData\Roaming\Microsoft\AddIns
Which is this in my case:
C:\Users\Mohamed\AppData\Roaming\Microsoft\AddIns
Shown here:
https://imgur.com/a/0toSYYA

Comment by: Jan Karel Pieterse (28-11-2022 10:55:00) deeplink to this comment

Hi Mohamed,

Have you tried to enter a different character than the question mark in settings for the Visualize options?

If you mostly use the ribbon buttons, you could also just remove that question mark.

Comment by: Mohamed GadAllah (28-11-2022 20:44:00) deeplink to this comment

Hi Jan,
remove that question mark solved the issue.

May I continue using the tool for education and personal purposes after 30 days?

Comment by: Jan Karel Pieterse (28-11-2022 22:40:00) deeplink to this comment

Hi Mohamed,

Yes of course!

Comment by: Mohamed GadAllah (29-11-2022 17:34:00) deeplink to this comment

Hi my friend.
Please, what are the limitations ?
Thanks

Comment by: Jan Karel Pieterse (29-11-2022 17:49:00) deeplink to this comment

Which limitations are you thinking of?

Comment by: Mohamed GadAllah (29-11-2022 17:51:00) deeplink to this comment

I mean this line:
"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."

Comment by: Jan Karel Pieterse (29-11-2022 19:57:00) deeplink to this comment

Hi Mohamed,

Limitations like:
- No reporting option on most dialogs
- Only reports the top 3 most used functions
- Displays the settings form regularly
- Less dependency levels

Comment by: Renan Serafim (8-12-2022 03:38:00) deeplink to this comment

I would like to request a trial for myself.

Comment by: Jan Karel Pieterse (8-12-2022 10:50:00) deeplink to this comment

Hi Renan,

There is no need to request a trial, you can simply download the tool using the download link on this page.

Comment by: Ritz (24-3-2023 16:47:00) deeplink to this comment

I just purchased RefTreeAnalyser and cannot get it to run on the Mac.

It was running fine until I tried to input my license code and now it is stuck in a loop, giving me the error "Oops, An error has occurred in RefTreeAnalyser
Error:
Error -2147210493: Method 'Execute' of object 'WebClient' failed in Module modUpdate.ExecuteHttpRequest"

I have to Force Quit Excel using the Mac Activity Monitor.

I have deleted the whole directory, restarted Excel and reinstalled several times but I cannot get the error to go away.

Can you please help?

Best regards,
Ritz

Comment by: Jan Karel Pieterse (24-3-2023 17:38:00) deeplink to this comment

Hi Ritz,

I've replied to your email, please double-check your spam folders if it hasn't arrived.

Comment by: Mal Z (6-4-2023 21:28:00) deeplink to this comment

Hi, I just bought a license. For the Tracing Cell dependents and precedents, how do I fix the Levels to a certain number (e.g. 5) automatically without having to toggle it each time the window opens?

Comment by: Jan Karel Pieterse (6-4-2023 23:08:00) deeplink to this comment

Hi Mal,

I'm afraid you currently can't. I decided to reset to 1 a long time ago because otherwise the tool may be very slow for larger models. Perhaps this calls for adding this as an option in the next update.

Comment by: Mal Z (6-4-2023 23:46:00) deeplink to this comment

That'd be a great addition to the next update - perhaps in the main settings menu, letting the user tick a box for a default Level they can choose.

Thanks a lot anyway!

Comment by: Jan Karel Pieterse (7-4-2023 11:27:00) deeplink to this comment

Hi Mal,

Noted, thanks!

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.