OK. OFFSET formulas are good: Check
Not able to ask client to update Master sheet: Check. (Clients can be such a pain. lol)
Create mirror "Master Data" sheet to pull data from: Check
How about macros? Can we use macros?
I have modified the example spreadsheet to include a Pivot Table to pull data from the "Master Data" table dynamically.
However, Pivot Tables have to be refreshed in order be updated.
The Pivot Table will automatically keep the data in the Filter/Sorted format needed for the formula in the Data Validation List to return the right values.
Included is a simple macro that refreshes the Pivot Table whenever a change occurs in the "Master Data" sheet.
This way you don't have to ask the client to update the Pivot Table every time new data is added to the "Master Data" array.
This simple macro goes in the "Microsoft Excel Objects" "Sheet2(Master Data)" code area:
I changed the "Category" dynamic name to look at the Pivot Table instead of the "Master Data" array:
I added another dynamic named range "MasterDataArray" for the Pivot Table to look at the "Master Data" array:
The Pivot Table has to look at the "Master Data" array via the "MasterDataArray" named range.
You can do this in the "Change Data Source" in the Pivot Table sheet. Set the "Change PivotTable Data Source to:
I realize this has gotten rather complicated. However, it does work.
Attached is the modified version of your example.
Test it out. Add new data to the "Master Data" array. See that the MD Pivot table updates and new Field Names are available in the pull downs.
Remember, this is not a macro enabled spreadsheet.
Let me know how it works for you and if changes are needed.
Cheers
Bookmarks