Named Range Drop Down Problem

[TomWOTSEC]

Hi all,

I would not profess to be an expert on Excel so this may be fairly simple.

I have a named range which then populates a drop down box on around 40 worksheets. From the dropdown selected it then populates about 5 cells next to the dropdown box with the relevant information using vlookup.

I now have to use this same structure and data for an alternative product, i try to change the values in the named range to reflect the new product... however it then throws up numerous errors based on the dropdowns on the worksheets still containing the previously selected product and presumably the vlookup then cannot find the relevant data... when i would like all the cells previously selected as... for example "Microsoft" which would then be changed to "Apple".

Is this something that can be done in any way? Or do i literally have to go through the whole 40 worksheets reselecting the new values? Which seems ridiculous and far too time consuming to be practical.....

Many thanks for your help everyone. If you need anything else, please let me know.

[Miraun]

If you have a new product, I'm a bit surprised that you would touch that initial named range that was used for the other 40 worksheets. Why not make a whole new reference named range to use with the new product? You can pick and choose parts of that initial reference data that is still valid, and copy it over, but I'm not sure why you'd try to change the original information.

[TomWOTSEC]

Basically i have saved a new copy of the same excel document as to make the changes.

The data contained in the 40 worksheets is all exactly the same and does not need to be changed...the only differences will be the data that is enterred into the first worksheet which then populates all the dropdowns on every worksheet and the vlookups that reference back to the table which is again on the first worksheet.

(I would upload a sample if that was possible but this is all sensitive information)

[TomWOTSEC]

Anyone have any ideas on this?

Basically once i change the name in the named range, it changes it in the dropdown box but keeps the old value selected, i want it to automatically update to the new value. Is this possible? or do i have to go through the whole worksheet, reselecting every single dropdown box to make it work?