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

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.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"Dean" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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" wrote:

> 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
>
>
>

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.

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis)> wrote in message
news:7A8CBCEE-1823-4742-B512-(E-Mail Removed)...
> 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" wrote:
>
>> 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
>>
>>
>>