Trace Precedents Tool

<-Drill · Aug 26, 2008

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]

Andrew Fergus · Aug 26, 2008

Re: Trace Precendents Tool

Hi

I'm curious : why did you post this anonymously? If you have been answering questions here for some years then it may have been beneficial to post this under your original user name.

Without a character assessment, or the add-in password, I (and more to the point, my employer) have a concern with security and what the code is doing. Whilst I would love to try this tool for the same reasons you outlined, unfortunately I have to err on the side of caution until I know more about this.

Good luck
Andrew

<-Drill · Aug 26, 2008

Re: Trace Precendents Tool

That's a fair point. Basically, I rolled this out in my office first and wanted to share it but I'm not particularlly interested in having my coworkers who visit this site seeing a) how much time I spend here and b) what questions I ask/answer. My usual login doesn't contain my real name either.

That said, I'm not trying to push this on anyone and I understand the concern you mentioned. I PM'd this to a few regulars when I was working on it to get their input and I'll PM to members like you that have been around a long time if you want.

I was going to post this in the Lounge. Usually, you don't see unsolicited "solutions" on this board, but we all give code and occasionally tell posters to put it in an xla or Personal Macro folder. Code alone wouldn't make a lot of sense without the userform. That's why it's a download, but like I said, I'll email it to someone who PM's me.

Andrew Fergus · Aug 26, 2008

Re: Trace Precendents Tool

Hi

Thanks for clarifying that - sorry if I put you into a tight spot.

The download worked just fine - I simply haven't installed it (yet), but I look forward to giving it a go! It's good to see some proactive suggestions/solutions being put forward on the Board....

Andrew

Andrew Fergus · Aug 27, 2008

Re: Trace Precendents Tool

Hi

I installed this on my home PC and that's a nice tool. Very handy.

A couple of (constructive) suggestions:
1. Can you put the current cell address next to the word 'Current Cell'? I found myself searching to find which cell was currently selected.
2. Having the ability to change the sort order would be handy - I don't know about other users but when I sort a list I like to see the latest item at the top. Not a biggie but it might be handy.
3. What would be really awesome is a 'back' button which means you could automatically go back to the previous cell you were looking at. Then you could go down a certain path and then back up a few cells and then down the next path and so on without reverting to either the original cell or the history tab. It would work much like the back button on say a web browser or windows explorer.

I also like the part where you can select another cell on the worksheet and then click select to start a new path. The other nice feature is the way the functions are split into logical parts in the display window. This tool will be very useful.

Thanks
Andrew

<-Drill · Aug 27, 2008

Re: Trace Precendents Tool

Andrew Fergus said:
Hi

I installed this on my home PC and that's a nice tool. Very handy.

A couple of (constructive) suggestions:
1. Can you put the current cell address next to the word 'Current Cell'? I found myself searching to find which cell was currently selected.
2. Having the ability to change the sort order would be handy - I don't know about other users but when I sort a list I like to see the latest item at the top. Not a biggie but it might be handy.
3. What would be really awesome is a 'back' button which means you could automatically go back to the previous cell you were looking at. Then you could go down a certain path and then back up a few cells and then down the next path and so on without reverting to either the original cell or the history tab. It would work much like the back button on say a web browser or windows explorer.

I also like the part where you can select another cell on the worksheet and then click select to start a new path. The other nice feature is the way the functions are split into logical parts in the display window. This tool will be very useful.

Thanks
Andrew

Thanks Andrew, Glad you liked it.

I appreciate the suggestions.
1)The cell Address can be found in the Current Cell Path, but I see your point. I can, and have, put the simple address (eg. "A5") next to the Current Cell header I may put it up in the Form header too.
2) I need to investigate this further. I want to avoid needing registry edits to remember individual customizations/choices. I understand there may be a way to create/reference a text file with said choices. I will investigate.
3) Great idea. I already implemented it. However, the way it works (now) is it only goes back to the most recent cell. So, if you hit Back repeatedly, you will toggle between two cells. More like Back on your TV and less like the internet. Certainly not as useful, but it's a start. Do you know of a way to link a userform button to run when the "Backspace" key is pressed (Application.OnKey maybe). Right now it works with Alt-B, but how cool would it be to use backspace? [this question may be more appropriate for a new thread, I'm just not sure]

In retrospect, the distribution of this isn't great. Any changes I make will need to be uploaded which will hose up my links in the original post (which I can't edit). Let me think about a different delivery system. But right now, it's seems to be just the two of us.

<-Drill · Aug 27, 2008

Re: Trace Precendents Tool

I think I've answered the Backspace issue.

I'm using

Code:

Private Sub Listbox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 8 Then BackButton_Click
End Sub

I guess I can copy this Keydown for every button/list/field on the Form, but that seems like a haze. I can't seem to make it work with a userform_active type event.

starl · Aug 28, 2008

Re: Trace Precendents Tool

Just a note that Admin has verified Drill is a member of this forum in good-standing.

<-Drill · Aug 29, 2008

Re: Trace Precendents Tool

Andrew Fergus said:
Hi

3. What would be really awesome is a 'back' button which means you could automatically go back to the previous cell you were looking at. Then you could go down a certain path and then back up a few cells and then down the next path and so on without reverting to either the original cell or the history tab. It would work much like the back button on say a web browser or windows explorer.

I think I have this working as invisioned now. You can drill down all the way to the source then back all the way to the Origin using the Back button, Alt-B or Backspace on the keyboard. I'll distribute next week.

Happy labor day everyone in the USA.

<-Drill · Sep 2, 2008

Hi all,

I have change the upload link. The links in the original post may not be working yet (I've asked the mods to fix).

The revised add-in can be downloaded here: http://www.mediafire.com/Drill

[Note: this link is out of date. See Post #1. --moderator]

Trace Precedents Tool

<-Drill

New Member

Excel Facts

Andrew Fergus

MrExcel MVP

<-Drill

New Member

Andrew Fergus

MrExcel MVP

Andrew Fergus

MrExcel MVP

<-Drill

New Member

<-Drill

New Member

starl

Administrator

<-Drill

New Member

<-Drill

New Member

Similar threads

Forum statistics

Share this page

Trace Precedents Tool

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

New Member

New Member

Administrator

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock