Trace Precedents Tool

[<-Drill]

Hi all. I've been asking and answering questions on the board under a different name for several years and I wanted to share a tool I've been working on that I think may be useful to the members here.

I've seen a lot of questions regarding precedents on other sheets. I've tried to make a tool that helps with this issue. I got the idea after using Aaron Blood's excellent Explode tool. While this doesn't do many of the things that Explode can do, it can work with precedents in other files, even if the file is closed.

It's an add-in that I call "<-Drill."

This is a work in progress that doesn't work with every formula, so any comments are welcome but don't expect immediate tech support.

So, without further ado, I give you [FONT=verdana, arial][SIZE=-1]<-Drill[/SIZE]

<-Drill is a drill-down automation tool. In excel, you can use "Cntl + [" to go to the first reference in a formula or cell, but that does not help you get to any other reference or return to your original cell or track where you have been. Excel has a built in Trace Precedent tool, but with that you have to click on arrows to navigate to the cell and then see what's there and maybe follow another arrow, etc. Also, the Trace Precedent tool has severe limitations when working with links to other files. With <-Drill, instead of clicking on arrows, you work in the <-Drill window.

<-Drill will parse a formula into all of its components/references. With it, you can see the value of referenced cells or navigate to them to see their formula or link. All the while, <-Drill will maintain a log of where you've been (a drill-down history).

When you use <-Drill on a formula, you get a listing of all cells that are referenced/linked by a formula along with their value. You can jump to the referenced/linked cell, edit the contents and see the results in seconds!

When you open <-Drill, a window appears that contains all the components of the formula in the selected cell. If you double-click on one of the items in the Formula Components / References listbox (or single click and hit the "Select" button), Excel will go to that cell or range, opening the file if necessary. You may access your <-Drill history of the History tab. From there, you can select any previously accessed cell. You may also you the "Back" button to step back one cell at a time. [newly added]

The Formula Components / References listbox contains the path information for each component in the cell. The value of each component is displayed in the 'Value' field.

To Access <-Drill

• Right-click cell, select "<-Drill"
• Ctrl + Shift + D

Navigating <-Drill

• Tab and Shift + Tab move around the form to the various buttons and clickable windows.
• ESC will close the form.
• Enter will 'click' the active button ("Select" is the default). The active button has a slightly darker border.
• Alt+S will 'click' the "Select" button on both the <-Drill and History tabs.
• Alt+O will 'click' the "Go to Origin" button on the <-Drill tab.
• Alt+B of Backspace on the keyboard will 'click' the "Back" button on the <-Drill tab. [newly added]
• Alt+E will 'click' the "Exit" button on both the <-Drill and History tabs.
• Ctrl + Tab will toggle between the <-Drill tab, the History tab and the Help tab.

Functionality
<-Drill is design to allow the user to quickly and easily see all the components and references in a selected cell.

Users can see the value of each of the components (if single cell, number or named range) by single clicking on the listed component.

By double-clicking a component or single-clicking then clicking the 'Select' button, the referenced cell, sheet, or file will be selected (and opened, if required).

With <-Drill open, users have the freedom to select other cells on a worksheet and update the <-Drill information by clicking "Select."

The History tab keeps a record of all cells visited during the active session of <-Drill. Users can return to any listed cell by double-clicking the reference or single-clicking and clicking "Select."

You may also you the "Back" button to step back one cell at a time. [newly added]

Formula names and hard-coded numbers are listed as components, but cannot be navigated to or activated.

Cell values are either formatted as currency or a number with two decimal places (#,##0.00) or as a date (mm/dd/yyyy). Formats are not always accurate .
Note: values for a range of cells will not be returned.

Layout

• <-Drill Tab
  • The <-Drill tab is broken into 3 sections: (1) Current Cell, (2) Formula Components/References, (3) Original Cell

Current Cell Section
This section will show the:
1. The formula in the current cell
2. The value of the current cell
3. The path (or location) of the current cell
Note: This information will update whenever a new cell is selected through <-Drill.

Formula Components / References
1. This section contains a list of all the components in the current cell's formula.
2. The value of each component will be listed in the 'Value' box, as available.

Original Cell Section
1. This section will show the formula in the original cell, the value of the original cell and the path (or location) of the original cell.
2. This information will not change during a single active session of <-Drill.
3. The 'Go to Origin' button will always bring the user back to the initial cell from which <-Drill was run.

• History Tab
  • The History tab will list the path of all cells that have been selected, activated or navigated to during the active session of <-Drill

The add-in can be downloaded here: [FONT=verdana, arial][SIZE=-1]<-Drill
or cut and paste this into your browser's address bar: [/SIZE][/FONT]https://www.dropbox.com/s/nwfhr6ahwb2ymvw/Drill.xla

Enjoy!




[/FONT]

 

[<-Drill]

Hi all,

This has been available for a month now, and the add-in has been downloaded about 40 times.

I'm just curious about people reaction/question/comments/suggestions.

 

[mordrid]

Hi I have downloaded and used it to learn my way round someone elses spreadsheet model I was given to maintain and update. It has saved me hours of work so thanks very much.

It is easy to understand and use and the only comment / suggestion I would have since I have worked in a workbook with 21 sheets and over 100 links that I had to trace and document so that I could understand the impacts is, is there anyway to search a spreadsheet and produce a 'links summary sheet' providing details of all the links within the workbook.

Could be a good update!!!

Richard

 

[<-Drill]

Richard,

Thanks for the feedback, glad you've found a good use for it.

Do you mean (1) a list of each file that is linked or (2) a comprehensive list of every cell that has links and what the links in each cell are?

Option 1 is very easy, option 2 is not.

 

[mordrid]

Hi Drill, I agree option 1 is easy but not sure of the benifit, option 2 was what I was on about , I realise it is not easy but I was thinking about how it may be possible when you posted Drill so I took the easy option and used your add-in to good effect.

For my benifit I was looking at trying to do option 2 to aid with trouble shooting other peoples models at work which I regularly get asked about and as with anything nobody uses the same conventions for putting SS together.

Before you posted drill I was trying to think if there was an easy way using VBA to do a loop to check cells for formulas and then summerise these cells on a new sheet, but again realised that this would not be easy. My knowledge didn't even get me off the starting block with how to identify if cell had formula in, once I have that I was looking to just C&P to another sheet and then do some sort of search for common cells in the formulas to sort etc until I had a consolidated list of cell formulas and the links associated with them. But as you say not easy!!

Richard

 

[<-Drill]

Richard,

That could result in a massive undertaking depending on the size and scope of the workbook. I'm going to punt and say this out of scope for this project; but it would be an interesting topic in the Help board.

 

[mordrid]

Hi, I am not surprised about it being out of scope and I agree it would be a big undertaking. However before posting to the help board I will have to think about what I want to achieve so that I can formulate the question to set me on the right road. I guess the start point would be the vba loop

Richard

 

[lox1985]

I know its been awhile since somebody talked about this. But this tool is awesome. I wish it worked for every formula but it works for most unless its a huge formula.

Thanks for this little add in.

 

[<-Drill]

Thanks for the comment. I'd love to fix any problems, but I haven't had much feedback. Over 100 downloads and 3 users have left comments.

I've noticed it sometimes starts with the wrong cell, but that is fixed by esc, ctrl+shift+T again.

 

[<-Drill]

Hi all,

5 years later and I'm still using this tool and have made a few adjustments to it. I've also added some functionality to evaluate parts of a formula (similar to hitting F9 when editing a formula).

The download source has been changed to dropbox.

https://www.dropbox.com/s/nwfhr6ahwb2ymvw/Drill.xla

Any and all comments or questions are welcome.

<-Drill

 

[lox1985]

Downloaded it. I lost the addin when moving jobs. Thanks for the bump