# Thread: Stepping thru a formula (Excel XP)

1. ## 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. ## Re: Stepping thru a formula (Excel XP)

Does Tools, formula auditing, evaluate formula help at all?

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