Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Stepping thru a formula (Excel XP)

    Hi Everyone,
    I have a workbook with thousands of formula's. I need to be able to go to any cell that contains a formula and "Step Thru" the formula and have each step take me to where it gets its information from.
    Example:
    =IF($A175="","",VLOOKUP($A175,'SAP Employees'!$A$1:$O$607,4,0))
    In the above formula I would like to go to "SAP Employees" sheet automatically to see what information is coming from that particular cell. I have been doing it manually, but it is taking forever.
    Any help would be deeply appreciated. Thanks in advance...
    Rick

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stepping thru a formula (Excel XP)

    Does Tools, formula auditing, evaluate formula help at all?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stepping thru a formula (Excel XP)

    Hi Jan,
    Thank you for getting back to me on this. I have tried that approach, but again, it is really not effective. I thought there might be a macro, or an add-in that someone might have, or know of, that would help me get thru all of these formulas a bit faster. Thank you
    Rick

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Stepping thru a formula (Excel XP)

    I am not clear why you need to physically look at the sheet:

    Why don't you just put in an empty column next to it:
    =VLOOKUP($A175,'SAP Employees'!$A$1:$O$607,1,0))

    This will give you the value it finds (which should = A175)

    You can extract any of the values from 1 (A) to 15(O) with the appropriate number of blank columns. If you extract them, why do you need to physically see them on the other sheet?

    You could write a macro to take the value in A175, use MATCH to find it in 'SAP Employees'!$A$1:$A$607 and then goto that cell if you really want. If you dblclick on column B (change as desired in the intersect line) in the row of interest it will goto the cell in the row it is "found" and column 4.

    <pre>Option Explicit
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
    Dim lRow As Long
    Dim vValue As Variant
    Dim rList As Range
    Dim wks As Worksheet
    Set wks = Worksheets("Sap Employees")
    Set rList = wks.Range("A1:a607")
    vValue = Cells(Target.Row, 1)
    lRow = 0
    On Error Resume Next
    lRow = Application.WorksheetFunction.Match(vValue, rList, 0)
    On Error GoTo 0
    If lRow = 0 Then
    MsgBox vValue & " is not found"
    Else
    wks.Select
    wks.Cells(lRow, 4).Select
    End If
    Set rList = Nothing
    Set wks = Nothing
    Cancel = True
    End If
    End Sub</pre>


    The macro does not go in a normal module but in the sheet object with the formula.
    Steve

  5. #5
    New Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stepping thru a formula (Excel XP)

    Hi Steve,
    Thank you for your response. I tried this code and it works for that particular column. I tried to copy the code for the next column and change the range ("B:B") to ("C:C"), and change the worksheet to another worksheet and I got errors. But keep in mind, I have 218 worksheets, each with at least 3 to 4 thousand formulas on it. There is one worksheet that has over 17,000 formulas. These are all in 1 workbook. You are right, I probably don't have to physically look at the sheet. But, if I have to write a formula in an empty column next to it, assuming there is an empty column next to it, then I might as well look at every formula and step thru it manually. I think both procedures will take about the same amount of time. I am not sure if there is an easy, and timely, way to verify that all the formulas are correct.
    But, I do thank you for your time and thoughtfullness.
    Rick

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Stepping thru a formula (Excel XP)

    I don't understand the need to "verify" that it is correct.

    If you want to manually look at each one, why do you need the formula? just look it up and copy the result when you are checking. Then you know it is correct.

    Personally, I would verify that the formula is doing what I expect it to do, then copy it down and trust the formula, but that is me...

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •