Matching Parent Range To Child Ranges (and Match Sizes)

    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.

    Re: Matching Parent Range To Child Ranges (and Match Sizes)


    cochese,


    I've got two suggestions.
    1) Don't let anyone but you add products.
    2) Try setting up a sheet with all the data and then just report on the data.


    Jim

    Re: Matching Parent Range To Child Ranges (and Match Sizes)


    I like your ideas...I just wish my boss did. I'm trying to follow his outline of how he wants it...it's getting incredibly tough.

    Re: Matching Parent Range To Child Ranges (and Match Sizes)


    You can always use the find feature for this. Select all of the sheets, you can even right click a sheet and choose "Select all sheets." Then hit ctrl & F and in the dialog box, enter the value you want to find, then on the replace tab, enter the new value. Click the button "Replace All" and you will be on your way. Record these actions and you will be on your way to doing it with VBA.

    Re: Matching Parent Range To Child Ranges (and Match Sizes)


    cochese,


    If the boss has decided how the REPORT should look, that shouldn't keep you from using my second idea. Collect all the data in one place and then create the reports that match the REPORT the boss likes based on the one source of data.


    If, on top of specifying how the reports should look, the boss has said how the data collection should look, you might descretely question him/her on whether she/he realizes that the data collection tool/page/sheet does not have to be the same as the reporting tool/page/sheet. That is, after all, why Excel is so useful. It's easy to format, AND you can do formulas and queries and other neat stuff.


    Jim

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login