A friend wants the list of potential choices to be on a different worksheet
than where he is choosing the value, via data, validation, list. I told him
to merely echo that list onto the current worksheet but he thinks that is
too inelegant!

It seems that EXCEL's help suggests if you somehow range name the list, it
can be on another worksheet, but it doesn't seem to work. Any suggestions,
including simple techniques (non macro) other than data, validation, list?

If the list is not too long, I suggest to specify the choices directly in
the data validation dialog box. Instead of pointing to a range (which must
be on the same sheet), you can directly list your choices in the field
'Source' (must be comma delimited).

Cheers,

Joerg Mochikun


"Dean" wrote in message
...
A friend wants the list of potential choices to be on a different
worksheet
than where he is choosing the value, via data, validation, list. I told
him
to merely echo that list onto the current worksheet but he thinks that is
too inelegant!

It seems that EXCEL's help suggests if you somehow range name the list, it
can be on another worksheet, but it doesn't seem to work. Any
suggestions,
including simple techniques (non macro) other than data, validation, list?

I think he wants the list to be dynamic.

Here's what EXCEL help says, which seems to be misleading (see note #3):

To create a drop-down list from a range of cells, use the Validation command
under the Data menu.

1. To create a list of valid entries for the drop-down list, type the
entries in a single column or row without blank cells. For example:


A

1
Sales

2
Finance

3
R&D

4
MIS


2. Note You may want to sort the data in the order that you want it
to appear in the drop-down list.

3. If you want to use another worksheet or another workbook, do one
of the following:

Use a different worksheet in the same workbook Type the list on that
worksheet, and then define a name (name: A word or string of characters that
represents a cell, range of cells, formula, or constant value. Use
easy-to-understand names, such as Products, to refer to hard to understand
ranges, such as Sales!C20:C30.) for the list.

How?

1.Select the cell, range of cells, or nonadjacent selections (nonadjacent
selection: A selection of two or more cells or ranges that don't touch each
other. When plotting nonadjacent selections in a chart, make sure that the
combined selections form a rectangular shape.) that you want to name.

2.Click the Name box at the left end of the formula bar (formula bar: A bar
at the top of the Excel window that you use to enter or edit values or
formulas in cells or charts. Displays the constant value or formula stored
in the active cell.).



Name box

3.Type the name for the cells, for example, ValidDepts.

4.Press ENTER.

Note You cannot name a cell while you are changing the contents of the
cell.

Use a different worksheet in a different workbook Type the list on that
worksheet, and then define a name with an external reference to the list.

How?

"Joerg" wrote in message
...
If the list is not too long, I suggest to specify the choices directly in
the data validation dialog box. Instead of pointing to a range (which must
be on the same sheet), you can directly list your choices in the field
'Source' (must be comma delimited).

Cheers,

Joerg Mochikun


"Dean" wrote in message
...
A friend wants the list of potential choices to be on a different
worksheet
than where he is choosing the value, via data, validation, list. I told
him
to merely echo that list onto the current worksheet but he thinks that is
too inelegant!

It seems that EXCEL's help suggests if you somehow range name the list,
it
can be on another worksheet, but it doesn't seem to work. Any
suggestions,
including simple techniques (non macro) other than data, validation,
list?

Dean, I had the same problem, but it helps reading the Excel Help twice.
It's quite easy:
1) Assuming that your list on Sheet2 is named 'Fruits'
2) Go to the cell on Sheet1 where you need the drop down list of valid
entries
3) As source for your valid data use '=Fruits' (i.e. without the the sheet
name)

That worked for me.

Cheers,
Joerg Mochikun

see your point. Whatever Excel Help says, as soon as you try to point to a
range on a different sheet, you probably get the same warning message that I
get "You may not use references to other worksheets or workbooks for Data
Validation criteria".


"Dean" wrote in message
...
I think he wants the list to be dynamic.

Here's what EXCEL help says, which seems to be misleading (see note #3):

To create a drop-down list from a range of cells, use the Validation
command
under the Data menu.

1. To create a list of valid entries for the drop-down list, type
the
entries in a single column or row without blank cells. For example:


A

1
Sales

2
Finance

3
R&D

4
MIS


2. Note You may want to sort the data in the order that you want
it
to appear in the drop-down list.

3. If you want to use another worksheet or another workbook, do one
of the following:

Use a different worksheet in the same workbook Type the list on that
worksheet, and then define a name (name: A word or string of characters
that
represents a cell, range of cells, formula, or constant value. Use
easy-to-understand names, such as Products, to refer to hard to understand
ranges, such as Sales!C20:C30.) for the list.

How?

1.Select the cell, range of cells, or nonadjacent selections (nonadjacent
selection: A selection of two or more cells or ranges that don't touch
each
other. When plotting nonadjacent selections in a chart, make sure that the
combined selections form a rectangular shape.) that you want to name.

2.Click the Name box at the left end of the formula bar (formula bar: A
bar
at the top of the Excel window that you use to enter or edit values or
formulas in cells or charts. Displays the constant value or formula stored
in the active cell.).



Name box

3.Type the name for the cells, for example, ValidDepts.

4.Press ENTER.

Note You cannot name a cell while you are changing the contents of the
cell.

Use a different worksheet in a different workbook Type the list on that
worksheet, and then define a name with an external reference to the list.

How?

"Joerg" wrote in message
...
If the list is not too long, I suggest to specify the choices directly
in
the data validation dialog box. Instead of pointing to a range (which
must
be on the same sheet), you can directly list your choices in the field
'Source' (must be comma delimited).

Cheers,

Joerg Mochikun


"Dean" wrote in message
...
A friend wants the list of potential choices to be on a different
worksheet
than where he is choosing the value, via data, validation, list. I
told
him
to merely echo that list onto the current worksheet but he thinks that
is
too inelegant!

It seems that EXCEL's help suggests if you somehow range name the list,
it
can be on another worksheet, but it doesn't seem to work. Any
suggestions,
including simple techniques (non macro) other than data, validation,
list?

Indeed, it does. Kind of odd that you can't navigate to another worksheet,
but can choose a range name of cells in that other worksheet. I assume it
has something to do with the range name being global, or something like
that.

My picky friend will be happy with your elegant solution!

Thanks!
Dean

"Joerg" wrote in message
...
Dean, I had the same problem, but it helps reading the Excel Help twice.
It's quite easy:
1) Assuming that your list on Sheet2 is named 'Fruits'
2) Go to the cell on Sheet1 where you need the drop down list of valid
entries
3) As source for your valid data use '=Fruits' (i.e. without the the sheet
name)

That worked for me.

Cheers,
Joerg Mochikun

see your point. Whatever Excel Help says, as soon as you try to point to a
range on a different sheet, you probably get the same warning message that
I
get "You may not use references to other worksheets or workbooks for Data
Validation criteria".


"Dean" wrote in message
...
I think he wants the list to be dynamic.

Here's what EXCEL help says, which seems to be misleading (see note #3):

To create a drop-down list from a range of cells, use the Validation
command
under the Data menu.

1. To create a list of valid entries for the drop-down list, type
the
entries in a single column or row without blank cells. For example:


A

1
Sales

2
Finance

3
R&D

4
MIS


2. Note You may want to sort the data in the order that you want
it
to appear in the drop-down list.

3. If you want to use another worksheet or another workbook, do
one
of the following:

Use a different worksheet in the same workbook Type the list on that
worksheet, and then define a name (name: A word or string of characters
that
represents a cell, range of cells, formula, or constant value. Use
easy-to-understand names, such as Products, to refer to hard to
understand
ranges, such as Sales!C20:C30.) for the list.

How?

1.Select the cell, range of cells, or nonadjacent selections (nonadjacent
selection: A selection of two or more cells or ranges that don't touch
each
other. When plotting nonadjacent selections in a chart, make sure that
the
combined selections form a rectangular shape.) that you want to name.

2.Click the Name box at the left end of the formula bar (formula bar: A
bar
at the top of the Excel window that you use to enter or edit values or
formulas in cells or charts. Displays the constant value or formula
stored
in the active cell.).



Name box

3.Type the name for the cells, for example, ValidDepts.

4.Press ENTER.

Note You cannot name a cell while you are changing the contents of the
cell.

Use a different worksheet in a different workbook Type the list on that
worksheet, and then define a name with an external reference to the list.

How?

"Joerg" wrote in message
...
If the list is not too long, I suggest to specify the choices directly
in
the data validation dialog box. Instead of pointing to a range (which
must
be on the same sheet), you can directly list your choices in the field
'Source' (must be comma delimited).

Cheers,

Joerg Mochikun


"Dean" wrote in message
...
A friend wants the list of potential choices to be on a different
worksheet
than where he is choosing the value, via data, validation, list. I
told
him
to merely echo that list onto the current worksheet but he thinks that
is
too inelegant!

It seems that EXCEL's help suggests if you somehow range name the
list,
it
can be on another worksheet, but it doesn't seem to work. Any
suggestions,
including simple techniques (non macro) other than data, validation,
list?

Thanks for your feedback. I also learned something today...
Joerg

"Dean" wrote in message
...
Indeed, it does. Kind of odd that you can't navigate to another
worksheet,
but can choose a range name of cells in that other worksheet. I assume it
has something to do with the range name being global, or something like
that.

My picky friend will be happy with your elegant solution!

Thanks!
Dean

"Joerg" wrote in message
...
Dean, I had the same problem, but it helps reading the Excel Help twice.
It's quite easy:
1) Assuming that your list on Sheet2 is named 'Fruits'
2) Go to the cell on Sheet1 where you need the drop down list of valid
entries
3) As source for your valid data use '=Fruits' (i.e. without the the
sheet
name)

That worked for me.

Cheers,
Joerg Mochikun

see your point. Whatever Excel Help says, as soon as you try to point to
a
range on a different sheet, you probably get the same warning message
that
I
get "You may not use references to other worksheets or workbooks for
Data
Validation criteria".


"Dean" wrote in message
...
I think he wants the list to be dynamic.

Here's what EXCEL help says, which seems to be misleading (see note
#3):

To create a drop-down list from a range of cells, use the Validation
command
under the Data menu.

1. To create a list of valid entries for the drop-down list,
type
the
entries in a single column or row without blank cells. For example:


A

1
Sales

2
Finance

3
R&D

4
MIS


2. Note You may want to sort the data in the order that you
want
it
to appear in the drop-down list.

3. If you want to use another worksheet or another workbook, do
one
of the following:

Use a different worksheet in the same workbook Type the list on that
worksheet, and then define a name (name: A word or string of characters
that
represents a cell, range of cells, formula, or constant value. Use
easy-to-understand names, such as Products, to refer to hard to
understand
ranges, such as Sales!C20:C30.) for the list.

How?

1.Select the cell, range of cells, or nonadjacent selections
(nonadjacent
selection: A selection of two or more cells or ranges that don't touch
each
other. When plotting nonadjacent selections in a chart, make sure that
the
combined selections form a rectangular shape.) that you want to name.

2.Click the Name box at the left end of the formula bar (formula bar: A
bar
at the top of the Excel window that you use to enter or edit values or
formulas in cells or charts. Displays the constant value or formula
stored
in the active cell.).



Name box

3.Type the name for the cells, for example, ValidDepts.

4.Press ENTER.

Note You cannot name a cell while you are changing the contents of the
cell.

Use a different worksheet in a different workbook Type the list on
that
worksheet, and then define a name with an external reference to the
list.

How?

"Joerg" wrote in message
...
If the list is not too long, I suggest to specify the choices
directly
in
the data validation dialog box. Instead of pointing to a range (which
must
be on the same sheet), you can directly list your choices in the
field
'Source' (must be comma delimited).

Cheers,

Joerg Mochikun


"Dean" wrote in message
...
A friend wants the list of potential choices to be on a different
worksheet
than where he is choosing the value, via data, validation, list. I
told
him
to merely echo that list onto the current worksheet but he thinks
that
is
too inelegant!

It seems that EXCEL's help suggests if you somehow range name the
list,
it
can be on another worksheet, but it doesn't seem to work. Any
suggestions,
including simple techniques (non macro) other than data, validation,
list?