Worksheet relations

[Cam05050]

I've been helping an auditing team with their spreadsheet model recently and have been using the INQUIRE tab's "Worksheet Relations" ability to map out the connections between worksheets.

I am wondering if there is a way to get a complete list of cell references that represent all of the edges in the Worksheet relations graph. For example if you have two Worksheets "MarketingCosts" and "Profit&Loss", the edge between both of these nodes in the graph would represent all of the cell references connecting both worksheets.

Obviously you can do this manually, however, I am pretty sure Excel must record this information somewhere in order to draw the Worksheet Relations graph.

 

[shaowu459]

I don't quite understand your question, a screenshot of expeced results will be helpful. I assume VBA is a must to do this task.

May i know your orginal purpose for doing that? May be there will be alternative solutions for that.

 

[Cam05050]

The auditing team has a very large workbook with several worksheets (Marketing costs, P&L Actual, Forecast, etc). They regularly come to us (IT department) to fix their spreadsheets as they seem to lose track of references between worksheets (in formulas) as people leave the company, move into different teams, etc.

I have no issues solving the problem but want to find a faster solution.

In Excel there is an Add-On INQUIRE tab that allows you to visualize worksheet relations (No Macro or VBA required).

What I am trying to achieve is a fast way of determining which cells within a given worksheet reference another specific worksheet (An edge/line in the Worksheet relations graph between two worksheets).

I can't seem to find a short cut for this.