surely there is a quicker way!.......

  • Thread starter Thread starter Rob Etherington
  • Start date Start date
R

Rob Etherington

Hi all,

i am currently ploughing my way through LOADS of excel worksheets
making some fixes.
The majority of the work i am doing is this...
We have approx. 500 worksheets each of which containing approx 35
sheets. on these 35 sheets there are many occasions where a drop down
box is used with validation from a range of data. all of the data
ranges are listed on one sheet "Reference Data". In many case's some
of the drop downs contain wrong or incomplete information, so i am
going through every workbook and making the neccesary changes to the
reference data sheet.
i wondered if there was a way where i could make one reference data
sheet with all the correct data on it and insert that directly into
each work book so that all the drop downs still find the relevant
ranges of data on the New RD sheet as opposed to the old one?

i don't know if any of that makes sense to any of you, but if it does
i would apprieciate any help you could offer.

Kind Regards
Rob
 
I assume that the Data, Validations reference named ranges on the "Reference
Data" sheet. I think you can copy in a new Reference Data sheet and have
the validations find them if you first (1) delete all the list names through
the Define Name dialog and then (2) delete the old Reference Data sheet, and
finally (3) copy in the new Reference Data sheet (which must have the same
names as you deleted).

Doing this manually (especially deleting a bunch of names) may be more work
than you'd save over doing it the current way. But with a macro this can be
done pretty quickly...
 
Back
Top