0

I'm a relatively naive excel user who's been dropped in the middle of an ocean of cells. I've been tasked with coding the value of one cell in a workbook into python. Of course, this cell has multiple precedents, which have multiple precedents, etc.

This workbook has about 20 sheets. About half are just data, but the others do all sorts of cruel and wicked things to that data. The main sheet alone has hundreds of such function cells. I've now written hundreds of lines of code in python, and I have no clue how much there is left.

I'd really like to get an idea of how immense and interconnected this workbook is. Is there a way I can generate a 'precedent tree' from a cell to see how many levels deep it goes and how many cells it ultimately depends on?

1
  • display-the-relationships-between-formulas-and-cells-a59bef2b-3701-46bf-You can track precedents (support.office.com/en-us/article/… ) but that likely won't be enough for you. Easiest way would be to write a short python code which does it for you. Sep 16, 2018 at 5:55

1 Answer 1

1

There are two possible methods, first is Non-programming and second is programming (Macro).

Non-programming method:

  1. Click the Formula Tab.
  2. Find Formula Audit Group & Click Show Formula Icon, available at top right.
  3. Key Board Shortcut is Ctrl+`.

  4. In Formula Audit Group you find Trace Precedents, just click it to see the relationship.

Programming method (VBA Macro):

Sub ExtractAllFormulas()

    Dim sht As Worksheet
    Dim shtName
    Dim myRng As Range
    Dim newRng As Range
    Dim c As Range

ReTry:
    shtName = Application.InputBox("Write name of the new sheet to list all formulas.", "New Sheet Name")
    If shtName = False Then Exit Sub

    On Error Resume Next
    Set sht = Sheets(shtName)
    If Not sht Is Nothing Then
        MsgBox "This sheet already exists"
        Err.Clear
        Set sht = Nothing
        GoTo ReTry
    End If

    Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
    Application.ScreenUpdating = False
    With ActiveSheet
        .Range("A1").Value = "Formula"
        .Range("B1").Value = "Sheet Name"
        .Range("C1").Value = "Cell Address"
        .Name = shtName
    End With

    For Each sht In ActiveWorkbook.Worksheets
        If sht.Name <> shtName Then
            Set myRng = sht.UsedRange
            On Error Resume Next
            Set newRng = myRng.SpecialCells(xlCellTypeFormulas)
            For Each c In newRng
                Sheets(shtName).Range("A65536").End(xlUp).Offset(1, 0).Value = Mid(c.Formula, 2, (Len(c.Formula)))

                Sheets(shtName).Range("B65536").End(xlUp).Offset(1, 0).Value = sht.Name

                Sheets(shtName).Range("C65536").End(xlUp).Offset(1, 0).Value = Application.WorksheetFunction.Substitute(c.Address, "$", "")

            Next c
        End If
    Next sht
    Sheets(shtName).Activate
    ActiveSheet.Columns("A:C").AutoFit
    Application.ScreenUpdating = True
End Sub

How it works:

  1. Insert this VBA code as Module.
  2. As soon you Run it will display Inputbox.
  3. Write a New Sheet name & finish with Ok.

This code will list all the Formula in new sheet.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .