trace dependency

[zoharb]

Respected,
we can check dependencies of a formula that is in that same worksheet but is there any way to search for file on which dependencies are there in VLOOKUP
Zohar Batterywala

 

[plettieri]

Hello zoharb

Here might be some help with a few options I found

Yes, you can search for external dependencies in Excel, especially when using functions like VLOOKUP that reference other files. While Excel provides some built-in tools to check for dependencies, they might not be sufficient for finding all external references, especially in large and complex workbooks. Here's how you can do it:

Method 1: Using Excel's Built-in Tools​

1. Find Links:
   • Go to the Data tab.
   • Click on Edit Links in the Connections group.
   • This will show you a list of all the external workbooks that your current workbook is linked to.
2. Trace Dependents:
   • Select the cell with the formula.
   • Go to the Formulas tab.
   • Click on Trace Dependents in the Formula Auditing group.
   • This will show arrows pointing to the cells that depend on the selected cell. If the dependent cells are in another workbook, you will see a dashed arrow pointing to an icon representing the other workbook.

Method 2: Using VBA to Find External References​

You can use VBA to search for external references in formulas. Here's a VBA macro that will scan all the cells in the active worksheet and list any formulas that reference external workbooks:
vba

Sub FindExternalReferences()
Dim ws As Worksheet
Dim cell As Range
Dim externalRefs As Collection
Dim externalRef As Variant

' Initialize the collection to store external references
Set externalRefs = New Collection

' Loop through all cells in the active worksheet
For Each cell In ActiveSheet.UsedRange
If cell.HasFormula Then
' Check if the formula contains an external reference
If InStr(cell.Formula, "[") > 0 Then
On Error Resume Next
externalRefs.Add cell.Address & ": " & cell.Formula, cell.Address
On Error GoTo 0
End If
End If
Next cell

' Display the external references
If externalRefs.Count > 0 Then
MsgBox "External references found in the following cells:" & vbCrLf & Join(Application.Transpose(externalRefs), vbCrLf), vbInformation, "External References"
Else
MsgBox "No external references found.", vbInformation, "External References"
End If
End Sub

How to Use the Macro​

1. Press Alt + F11 to open the VBA editor.
2. Insert a new module by right-clicking on any of the existing modules or the workbook name in the Project Explorer and selecting Insert > Module.
3. Copy and paste the above VBA code into the new module.
4. Close the VBA editor and return to your Excel workbook.
5. Press Alt + F8 to open the Macro dialog box.
6. Select FindExternalReferences and click Run.

This macro will scan the active worksheet for any formulas that contain external references and display them in a message box.

Method 3: Using Excel's Find and Replace​

1. Press Ctrl + F to open the Find and Replace dialog box.
2. Click on Options to expand the dialog box.
3. In the Find what box, type
4. (without quotes). This will search for any formulas that contain an external reference.
5. In the Within dropdown, select Workbook to search the entire workbook.
6. Click on Find All.

This will list all the cells that contain external references. You can then review these cells to see which external files they reference.
By using these methods, you can effectively search for and manage external dependencies in your Excel workbooks.


hope this helps
plettieri