Subscribe in a readerWhy RefTreeAnalyser?
Easily navigate the reference tree
The formula auditing capabilities of Excel are limited in that they display a bunch of arrows from the current cell to it's dependents and/or precedents. Navigating these arrows is relatively hard to do, especially if there are more than just one off-sheet reference and the combination of workbookname and sheetname is long (you're unable to see the cell addresses):
The RefTreeAnalyser greatly eases the navigation of dependents and precedents. You simply click on a found reference in a simple tree-like structured view and the accompanying cell or range of cells is selected.
Quickly see what cells are referred to
Although Excel does enable you to see what cells precede your cell and/or what cells use the current cell, getting an overview of them is quite hard. See the example below...
The RefTreeAnalyser greatly simplifies this as well: you get one tree-like overview of all cells that depend on the active cell and all cells that precede the current cell (and both may go up to 5 levels deep down/up the dependency tree!). The screenshot below shows what the same analysis looks like with RefTreeAnalyser:
Locate all circular references
Excel does detect if a workbook contains circular references, but only enables you to navigate one.
The RefTreeAnalyser detects all your circular references and shows them to you in one -easy to navigate- treeview.
Generate a report quickly
Excel's formula auditing tools do not have any reporting options.
The RefTreeAnalyser comes with a Report option which writes the current analysis to a new worksheet in your workbook. The report may look like this:
Comments
All comments about this page:
Comment by: Jan Karel Pieterse (7/2/2009 12:18:31 PM)Hi Bill,
Yes it does. And if there are external links and the source file(s) are open those will be shown and travelled too.
Comment by: Chun Lee (11/9/2010 12:45:13 AM)Does the RefTreeAnalyser pinpoint the specific cell to break the loop?
Does the RefTreeAnalyser give suggestion on how do break the Circular Reference identified?
Comment by: Jan Karel Pieterse (11/9/2010 1:47:58 AM)Hi Chun Lee,
Not exactly. It shows you all cells that form the circle. It is up to you to decide how to break the circle, since that is too closely tied into your model for RefTreeAnalyser to assess.
A general advice on circular references:
Always include one cell within each circle which can function as a break-the-circle cell and (at the same time) enables you to set default values to start the iteration.
Typically, this cell would contain an IF function like this:
In this example:
BreakCircle: A named cell in which you can enter a 1 to break the circle and reset the values
DefaultValueCell: A named cell that holds the starting value for the iteration
PreviousCircleCell: The previous cell in the circle
Comment by: Chun Lee (11/11/2010 11:13:56 PM)Hi!
I use the RefTreeAnalyser to find the circular reference in the individual worksheet of a workbook. The result is no circular reference is being found. I separate the worksheet from the workbook by creates a new worksheet in a new book.
But, the RefTreeAnalyser found many circular references when the RefTreeAnalyser is used on the workbook. It hangs after the record show more than 200 circular references. Please advise how to solve this problem. I would like to break or avoid these circular references. Thanks.
Comment by: Jan Karel Pieterse (11/12/2010 12:46:24 AM)Hi Chun Lee,
By separating the worksheet maybe you have broken the circular references. It is also possible that you created circular references accross several workbooks. If the workbook contains links to other workbooks, make sure you open all linked files before running the circular reference check.
Comment by: karu (12/21/2011 4:10:16 AM)As I am working on excel and I have to see many signal are dependent on many things which are coming from multiple sheets.so I have to do explode and see and copy manualy nad prepare the equation in a sheet.so I want that if I explode one signal then all dependents from multiple sheet should be fetched and get added in a particular sheet.
If any one could do it pls mail me at sawan.cute@gmail.com It will be gr8ness.
will be looking forward for your reply
Comment by: Bill Benson (12/30/2011 3:08:24 PM)JKP, Hi again. Does this software allow you to navigate to ranges in the precedents and/or dependents area while KEEPING the original item (and list of precedents and/or dependents) so that one may fully analyze a single starting point to one's satisfaction... or does each navigation cause an update to the analysis? If this question doesn't make enough sense to you, I might answer it for myself if I come back to this page and study a little harder what you're already saying the product does. Sure looks handy.
Meanwhile I am off to go play with Name Manager.
Bill
Comment by: Jan Karel Pieterse (1/1/2012 11:58:48 AM)Hi Bill,
It does not currently keep the analysis results, just the cells you analysed. If you don't dive in too many levels, it is fast enough to redo a cell though. Just DL the demo version, it should give a good idea.
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.