I have been racking my brains over this problem of mine for days now...and wasted a lot of hours I need back. What I have is a workbook with a master list of products, and then several other lists in the workbook that have to match this master list. That is, match the number of products & the name of each.
The complication comes from how all this is arranged in the book. I have attached a simplified version of how it is all arranged. I'll try summarizing the goal - there are X number of regions (each its own sheet), and in each region (sheet) are different periods (like Year To Date, Quarter To Date, etc). Under each period is the list of products (what I call the Child ranges), for a total of: number of regions X number of periods. If the product list changes the user has to change the master list, as well as each 'child range'. I have unsuccessfully tried to arrange it so when the master list is changed (added to/subtracted from) a macro can be ran that will adjust the child ranges to match the master list in size & product names.
I focused on using named ranges, and then just cell references, but I'm stuck now.