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<attarkon.ee )



"Dean" wrote in message
...
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
...
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