Data Validation redux

[Dean]

My client likes these drop downs (using data, then validation, then list),
but I notice that, if the list you want to use is on another worksheet, you
can't do it. Since this worksheet will be replicated 100s of times in the
workbook, I really don't want to have to have a copy of the list on every
such sheet. Is there some other similar way to have a pre-populated set of
choices for a cell, where the choices come from another worksheet? Also, I
have noticed that, when my biggest spreadsheets crash and go into repair
mode, it is always the data validation drop downs that get wiped out,
sometimes all the formatting, but not always, but always these drop downs
get wiped out. So, perhaps there should be (and is) a better way/

Thanks for any help!
Dean

 

[Arvi Laanemets]

Hi

You have to define the list as named range (I myself prefer dynamic named
ranges in such cases), and then you use this name as list source.

 

[Guest]

Setup to use a list on one worksheet as the source list for data validation
on other sheets in the same workbook:

Select the list to be used for data validation and give it a name. See
Excel help - use "Name a cell or range" (without quote marks) as the search
for to see how to do this. Let's presume you call this range, originally
enough <g>, myList.

Now when you set up your data validation in other sheets, choose List for
the Allow entry and in the Source entry, enter
=myList

That's all it takes to use a list in one sheet as the validation list in
other sheets.

As for your workbooks crashing, first piece of advice is "to crash is
typical, to have a recent backup is divine", so first recovery effort
involves having a good backup strategy.

If your range(s) to have validation or anything else is static, you could
use the Workbook_Open() event to run code to actually rebuild the formulas
each time the workbook is opened. You could record macros to perform the
actual operations and either copy the body of those macros into the _Open()
event code area or have it call those recorded macros by name. If/when you
record such macros, start with some sheet other than the one to set up
formulas/formatting/etc on selected, that way your recorded macro will start
off by selecting that sheet, assuring that the effects of the macro always
get applied to the proper sheet(s).

 

[Dean]

I tried it, but didn't know I needed to put an equal sign in front of the
range name. Silly me! Thanks to you and Arvi.