# 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.
> 
> ...


 
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


```
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]


----------



## <-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]


----------



## <-Drill (Sep 26, 2008)

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 (Sep 26, 2008)

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 (Sep 26, 2008)

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 (Sep 27, 2008)

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 (Sep 29, 2008)

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 (Sep 29, 2008)

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 (Jun 16, 2009)

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 (Jun 22, 2009)

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 (Nov 22, 2013)

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 (Nov 22, 2013)

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


----------



## <-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]


----------



## keker (Apr 1, 2014)

Hi

I spent some time looking for a tool that helps tracing precedents and dependants, that's great tool You've made, and that helps a lot.
But I have one problem using that tool. In my version of Excel (and for some other languages, too), I use "." to separate millions, thousands etc and "," to separate the decimal parts. So the numbers look like that: "123.332,23" rather than "123,332.23"
In the driil tool the number "429192,394" looks like "429192394222222.000" which makes the tool harder to use.

Would it be a problem either to add an option to choose between number format, or publish a second version of the tool for the others versions of Excel?

Regards
Maciek


----------



## <-Drill (Apr 1, 2014)

Hi Maciek,

Glad you found the tool somewhat useful.  

Can you tell me where the six 2s come from after 429192394?

Also, can you run this code and tell me what the response is?

Sub Decsep()
MsgBox Application.DecimalSeparator
End Sub


----------



## keker (Apr 2, 2014)

Hi

Thanks for the answer.
The "429192,394" is rounded to 3 numbers after comma (should be 429192,394222222), so the "429192394222222.000" is just the whole number. I hope the screenshots would explain it better (hope they show up):













It shows what the number look like in Drill Tool, and in Excel.

The response for the code is comma (,)

Regards
Maciek


----------



## <-Drill (Apr 3, 2014)

Thanks,

I've made some early headway, but it's not easy for me to test.  References are displayed/interpreted using periods as the decimal, and that sometimes screws up some math calculations.  The quick calc functioanlity is going to be tough, but I expect that isn't your primary need (it wasn't mine either, just a "i wonder if I can do it...").

Everything would be a lot easier if you just changed your Windows OS regional settings. 

I'm super busy with work and out next week, so progress may be a few weeks away.


----------



## keker (Apr 10, 2014)

Hi

Yes, I use the Drill without that feature, and it's still great piece of work.
I know the change of OS regional setting would fix the error, but I have to use the company defaults

Regards
Maciek


----------



## kisauni (Dec 11, 2015)

Hi 

I know its been a while since you posted this but I just found a link to it while searching on google.
I just registered so that I can thank you for producing this tool.  It really is great and makes it so much easier to track formulas.

Joel


<-Drill said:


> 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.


----------



## <-Drill (Dec 19, 2015)

Thanks Joel. Glad you found it. 

Now I'm trying to port it for use on OSX, that's proving difficult. 

Happy holidays.


----------

