Excel worksheets: summarize a bunch of sheets using indirect [closed]

Question

Current scenario

• I'm a data tester, and I test insurance contracts, and each policy has many "roles", "features", "history items" etc.
• A standard SQL query with all data joined will return 20-30 rows all jumbled together
• We need a way to review each policy (aka contract) in an organized and systematic way
• So, instead of creating 1 big query, we create 7 related queries. Each related query uses "contract #" as a PK (primary key). We put all the queries in a workbook and we're well on our way.

Issue

• The problem is, we still have a lot of data processing to do, we can review each "policy" across all these sheets
• We need a simple, efficient and highly automated method to review our Excel workbook

Answer 1

solution

• create a "summary" worksheet that connects all related worksheets
• this concept works for any data with a shared PK (primary key)
• all we're really doing is using standard VLookup functionality of Excel, but with an important twist
• instead of hard coding our VLookups we'll use Excel's INDIRECT function to execute our VLookups based on column headings
• the big advantage here is that we can add/remove/change columns and since our ODB (operational database) has 1,200 tables, we have a lot of variations (beyond the scope of this overly simplistic Q&A)