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]

 

[keker]

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]

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]

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]

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]

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]

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

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]

Thanks Joel. Glad you found it.

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

Happy holidays.