Grace
12/19/2006 5:18:00 AM
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" <no@email.address> wrote in message
news:em7kso$okn$1@daniel-new.mch.sbs.de...
> 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" <whooshbopbang4@adelphia.net> wrote in message
> news:gvCdnXTKqotK0BrYnZ2dnUVZ_tCtnZ2d@adelphia.com...
>> 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" <no@email.address> wrote in message
>> news:em7h5i$tmq$1@daniel-new.mch.sbs.de...
>> > 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" <whooshbopbang4@adelphia.net> wrote in message
>> > news:B6adnXspOuZT3RrYnZ2dnUVZ_sudnZ2d@adelphia.com...
>> >> 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?
>> >>
>> >>
>> >
>> >
>>
>>
>
>