|OK, I will try and explain how it all works together.|
My master (parent) file consists of one sheet which has all the data I need in the columns.
The columns are in sets of three the first being material size then next being surface area and finally the last column in each set is the weight per meter.
The first column in the sheet contains the section names which are also the names of the ranges in the first column of each data set.
As per your excellent advice previously I named the three columns of the data set “XXXXXDATA” which worked perfectly on the formula constructing the LOOKUP function from the single range name.
All my data is in three columns with two names and corresponding ranges. i.e ANGLE & ANGLEDATA.
At present there are ten sets of data (30 columns) that I use but will increase to potentially 50+ sets in the future.
As the data for these extra columns will be added on an ad-hoc basis the sheet is continually updated as required.
My child file has one sheet that is linked to the master and recalculates each time it is opened to ensure it is up to date with the master content at any time. My projects can each have up to 1000 child files linked to the master.
Each child file uses names and ranges which refer back to the link sheet to construct the drop down lists and calculate the value. These names and ranges are identical to the master file names and ranges.
Excel links and updates DATA automatically which is great.
Excel does not link or update names and ranges which is my problem.
I may increase the column ranges in my master file because I have added extra data making the columns longer, the child file automatically captures these changes in the data. At the moment the drop down lists show lots of white space because the ranges are set at greater values than the data existing. It is sometimes a nuisance but still easy to live with.
I will increase the data columns for new groups and this is automatically updated by the links because I have linked the master full width and a larger number of rows than I know will ever be used. I do not link the full sheet because of the huge amount of recalculation on updating.
The problem is that I want to copy the new names and ranges in the master and create the same values in the child files and also update the values for existing ranges if they have changed.
Two scenarios exist here. One is to compare the child name values with the parent name values and update the changes. Two is to just pick up ALL the names and ranges from the parent file and overwrite the values in the child file each time it opens.
If you have any more questions or would like a copy of the relevant files then I will be very happy to comply.