Results 1 to 6 of 6

20040902, 14:51 #1
 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

20040902, 17:48 #2
 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.jkpads.com
Professional Office Developers Association

20040902, 18:29 #3
 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 addin that someone might have, or know of, that would help me get thru all of these formulas a bit faster. Thank you
Rick

20040902, 19:16 #4
 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

20040902, 21:44 #5
 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

20040902, 22:14 #6
 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