Better trace precedents

[excel4]

I use trace precedents a fair bit to find errors but I just find it's not quite up to the job. It doesn't work across spreadsheets or with lookup formulas (it doesn't point to the cell thats actually looked up). Are there any better plugins out there? Does anyone else find this a problem or are there ways around this?

 

[Rick Rothstein]

I use trace precedents a fair bit to find errors but I just find it's not quite up to the job. It doesn't work across spreadsheets or with lookup formulas (it doesn't point to the cell thats actually looked up). Are there any better plugins out there? Does anyone else find this a problem or are there ways around this?

Are you talking about using trace precedents in VBA or manually on the worksheet. If manually on the worksheet, you can double click the dashed connecting line between the cell and the icon that looks like a mini-worksheet and it will bring up a "Go To" dialog with all the precedent cells listed from which you can select one and click OK to be taken to that sheet where the precedent cell or cells will be already selected.

 

[excel4]

I meant manually on the worksheet. The goto feature is useful and does link across sheets but I can't view all the precedents of a cell on one screen. I have to switch sheets which gets confusing as I have values on loads of different sheets.

 

[Rick Rothstein]

Here is a macro I posted once that substitutes the values from all cell references in a formula in place of the cell reference itself and then displays that in the cell next to it... see if it does what you want. The red highlighted line of code controls where the output goes in case you want to change the output location. Note that this code works against the selected cell or cells (assumed to be in a column if the output goes to the next cell to the right).

Sub CheckCellReferences()
  Dim ShapeCount As Long, Arrow As Long, Link As Long, Addr As String, Frmla As String
  Dim Cell As Range, CurrentCell As Range, OriginalSheet As String, OriginalCell As String
  Application.ScreenUpdating = False
  OriginalSheet = ActiveSheet.Name
  OriginalCell = ActiveCell.Address
  ShapeCount = ActiveSheet.Shapes.Count
  For Each Cell In Selection
    Set CurrentCell = Cell
    Frmla = Replace(CurrentCell.Formula, "$", "")
    If CurrentCell.HasFormula Then
      CurrentCell.ShowPrecedents
      Link = 1
      For Arrow = 1 To ActiveSheet.Shapes.Count - ShapeCount
        On Error Resume Next
        Do
          CurrentCell.Parent.Activate
          CurrentCell.Activate
          Addr = CurrentCell.NavigateArrow(True, Arrow, Link).Address
          If Err.Number Then
            Link = 1
            Exit Do
          End If
          Frmla = Replace(Frmla, ActiveCell.Address(0, 0), ActiveCell.Value)
          Frmla = Replace(Frmla, ActiveCell.Parent.Name & "!", "")
          Frmla = Replace(Frmla, "'" & ActiveCell.Parent.Name & "'!", "")
          Link = Link + 1
Continue:
        Loop
        Cell.Offset(, 1) = "'" & Frmla
     Next
      CurrentCell.ShowPrecedents Remove:=True
    End If
    Worksheets(OriginalSheet).Activate
    Range(OriginalCell).Activate
  Next
  Application.ScreenUpdating = False
End Sub

NOTE: If your formula has a text value that looks like a cell reference, for example the A12 in "Serial Number A12-345", and one of the cell references in the formula is actually A12, then the A12 inside the text constant will be replaced along with the actual cell reference in the formula... I do not know a way around this should it occur.<!-- google_ad_section_end -->

 

[excel4]

Thanks, that's really useful. It's quite like the evaluate formula tool. I've searched the plugins and I can't find anything more comprehensive. I think basically I'm after something that doesn't exist.

 

[excel4]

Great tool for understanding and fixing spreadsheets

So a while back I posted in here asking about better error tracing add-ins to no avail. Anyway after some googling I stumbled across this site, Slate. It's really useful for finding mistakes and just understanding sheets so I thought I'd share it back here.

Link to site - Slate

 

[Joe4]

Re: Great tool for understanding and fixing spreadsheets

I merged your post from today back with the original thread. It makes the most sense to put it there instead of starting a new thread.

 

[Derek Brown]

Re: Great tool for understanding and fixing spreadsheets

For a number of years I have been using Spreadsheet Detective whenever I need to analyse a workbook created by someone else. I have found it to be invaluable on many occasions.
There are a number of pricing options, but you will need to purchase a licence to use it when the 30-day trial period expires.
Have a look at The Spreadsheet Detective Excel 2010 Audit Formula Error Add-In

 

[<-Drill]

Re: Great tool for understanding and fixing spreadsheets

Hi, you could also try my old tool:

http://www.mrexcel.com/forum/genera...r-questions/338332-trace-precedents-tool.html

The download link in the original post appears dead, but you can use Drill or I can send it to you.

 

[<-Drill]

Re: Great tool for understanding and fixing spreadsheets

Hi, you could also try my old tool:

http://www.mrexcel.com/forum/genera...r-questions/338332-trace-precedents-tool.html

The download link in the original post appears dead, but you can use Drill or I can send it to you.

Media Fire is so last decade. Here is a dropbox link that should actually work: https://www.dropbox.com/s/nwfhr6ahwb2ymvw/Drill.xla