Hi All,
Long time consumer of tips in this forum achieved through google-fu, but I haven't had success finding an answer to this one...
Specific Question: How can I get the table of results of a Find and Replace dialog box Find all search output as text to put into a worksheet for downstream analysis.
Background (for context): I've been given a pretty complex and robust financial model (rows walk through elements, columns carry dates into the future) in excel that I am trying to understand. It has many tabs with many links and interrelationships. Being new to the process, I want to understand those connections and what relies on what. As such, I would really like to assemble effectively a network diagram of the worksheets (A feeds B; B feeds C, D, & E; D feeds back into A). My formula auditing skill is generally good (chasing through precedents), but many of the formulas are SUMIFs and the like - pointing to large ranges driven off of date inputs in the worksheet of the formula.
To achieve this, I have had success with wildcard searching within formulas of each worksheet one by one for "=*!", that is, for each worksheet, what formulas exist within it that link to other worksheets. I have been successful using the outputs of this to identify all external dependencies of the worksheet, but am drowning in the volume of results (frequently over 4,000 results since a formula will copy across many columns and be effectively the same, just with a different date input). I can look at the table of results and focus on the Cell column to identify results I can ignore (changing columns with consistent rows all effectively the same) and instead focus on rows that have changed to identify a new formula and new reference.
My life would be much easier if I could get this whole table in a worksheet. This would allow me to do some text splicing to summarize the rows with external worksheet references (e.g. I would immediately know that rows 11, 43, and 53 link to formulas that in other worksheets) and then be able to parse the formulas themselves to make sure they are effectively the same, and identify inputs (a date row) and outputs (row 57 in worksheet X) of the formulas to document them.
Hope my post was comprehensive enough, and I look forward to a specific solution or a suggestion for a better way to attack the problem!
Long time consumer of tips in this forum achieved through google-fu, but I haven't had success finding an answer to this one...
Specific Question: How can I get the table of results of a Find and Replace dialog box Find all search output as text to put into a worksheet for downstream analysis.
Background (for context): I've been given a pretty complex and robust financial model (rows walk through elements, columns carry dates into the future) in excel that I am trying to understand. It has many tabs with many links and interrelationships. Being new to the process, I want to understand those connections and what relies on what. As such, I would really like to assemble effectively a network diagram of the worksheets (A feeds B; B feeds C, D, & E; D feeds back into A). My formula auditing skill is generally good (chasing through precedents), but many of the formulas are SUMIFs and the like - pointing to large ranges driven off of date inputs in the worksheet of the formula.
To achieve this, I have had success with wildcard searching within formulas of each worksheet one by one for "=*!", that is, for each worksheet, what formulas exist within it that link to other worksheets. I have been successful using the outputs of this to identify all external dependencies of the worksheet, but am drowning in the volume of results (frequently over 4,000 results since a formula will copy across many columns and be effectively the same, just with a different date input). I can look at the table of results and focus on the Cell column to identify results I can ignore (changing columns with consistent rows all effectively the same) and instead focus on rows that have changed to identify a new formula and new reference.
My life would be much easier if I could get this whole table in a worksheet. This would allow me to do some text splicing to summarize the rows with external worksheet references (e.g. I would immediately know that rows 11, 43, and 53 link to formulas that in other worksheets) and then be able to parse the formulas themselves to make sure they are effectively the same, and identify inputs (a date row) and outputs (row 57 in worksheet X) of the formulas to document them.
Hope my post was comprehensive enough, and I look forward to a specific solution or a suggestion for a better way to attack the problem!
