Data Validation Lists Across Sheets

I have a data-validation list box. Is it possible for the source cells for
the list to be on another sheet from the one in which the list-box appears?

I know similar questions have been asked before (I poked around before I
submitted this), and one fellow suggested that a data-validation iist-box can
reference another sheet if the list on the other sheet is named. Sadly, I
don't know how to do this. If cell A1 on Sheet 1, for example, has a list box
whose source cells are Sheet 2, A1:A20, how do I assign a name to get the
validation to work? No matter what I do, I get a pop-up saying that
validation cannot reference other sheets. Help!

Use a named range, say MyList,
then put as the DV source: =MyList

Debra Dalgleish has good coverage on the steps at her:
http://www.contextures.com/xlDataVal01.html

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Michael Link" <[email protected]> wrote in message
news:[email protected]...
> I have a data-validation list box. Is it possible for the source cells for
> the list to be on another sheet from the one in which the list-box
appears?
>
> I know similar questions have been asked before (I poked around before I
> submitted this), and one fellow suggested that a data-validation iist-box
can
> reference another sheet if the list on the other sheet is named. Sadly, I
> don't know how to do this. If cell A1 on Sheet 1, for example, has a list
box
> whose source cells are Sheet 2, A1:A20, how do I assign a name to get the
> validation to work? No matter what I do, I get a pop-up saying that
> validation cannot reference other sheets. Help!

Is there a way to get a drop-down list to actually appear, though? I've named
the source cells, and the cell on the other sheet does accept only data from
those cells (thanks!), but I really need a drop-down box to actually appear.
Is that possible?

"Max" wrote:

> Use a named range, say MyList,
> then put as the DV source: =MyList
>
> Debra Dalgleish has good coverage on the steps at her:
> http://www.contextures.com/xlDataVal01.html
>
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "Michael Link" <[email protected]> wrote in message
> news:[email protected]...
> > I have a data-validation list box. Is it possible for the source cells for
> > the list to be on another sheet from the one in which the list-box
> appears?
> >
> > I know similar questions have been asked before (I poked around before I
> > submitted this), and one fellow suggested that a data-validation iist-box
> can
> > reference another sheet if the list on the other sheet is named. Sadly, I
> > don't know how to do this. If cell A1 on Sheet 1, for example, has a list
> box
> > whose source cells are Sheet 2, A1:A20, how do I assign a name to get the
> > validation to work? No matter what I do, I get a pop-up saying that
> > validation cannot reference other sheets. Help!
>
>
>

Michael,

Make sure you select "List" and check "in-cell dropdown"

HTH,
Bernie
MS Excel MVP


"Michael Link" <[email protected]> wrote in message
news:[email protected]...
> Is there a way to get a drop-down list to actually appear, though? I've named
> the source cells, and the cell on the other sheet does accept only data from
> those cells (thanks!), but I really need a drop-down box to actually appear.
> Is that possible?
>
> "Max" wrote:
>
>> Use a named range, say MyList,
>> then put as the DV source: =MyList
>>
>> Debra Dalgleish has good coverage on the steps at her:
>> http://www.contextures.com/xlDataVal01.html
>>
>> --
>> Rgds
>> Max
>> xl 97
>> ---
>> Singapore, GMT+8
>> xdemechanik
>> http://savefile.com/projects/236895
>> --
>> "Michael Link" <[email protected]> wrote in message
>> news:[email protected]...
>> > I have a data-validation list box. Is it possible for the source cells for
>> > the list to be on another sheet from the one in which the list-box
>> appears?
>> >
>> > I know similar questions have been asked before (I poked around before I
>> > submitted this), and one fellow suggested that a data-validation iist-box
>> can
>> > reference another sheet if the list on the other sheet is named. Sadly, I
>> > don't know how to do this. If cell A1 on Sheet 1, for example, has a list
>> box
>> > whose source cells are Sheet 2, A1:A20, how do I assign a name to get the
>> > validation to work? No matter what I do, I get a pop-up saying that
>> > validation cannot reference other sheets. Help!
>>
>>
>>

No, it's definitely not working. The data in the cell with the validation is
restricted correctly, because it only accepts data that appears in the source
cells for the list on the other sheet, but no drop-down box appears. (I
double-checked that "In-Cell Dropdown" was checked on the validation pop-up.)

I'm running this on Excel for Mac--I wonder if that has something to do with
it.

"Bernie Deitrick" wrote:

> Michael,
>
> Make sure you select "List" and check "in-cell dropdown"
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Michael Link" <[email protected]> wrote in message
> news:[email protected]...
> > Is there a way to get a drop-down list to actually appear, though? I've named
> > the source cells, and the cell on the other sheet does accept only data from
> > those cells (thanks!), but I really need a drop-down box to actually appear.
> > Is that possible?
> >
> > "Max" wrote:
> >
> >> Use a named range, say MyList,
> >> then put as the DV source: =MyList
> >>
> >> Debra Dalgleish has good coverage on the steps at her:
> >> http://www.contextures.com/xlDataVal01.html
> >>
> >> --
> >> Rgds
> >> Max
> >> xl 97
> >> ---
> >> Singapore, GMT+8
> >> xdemechanik
> >> http://savefile.com/projects/236895
> >> --
> >> "Michael Link" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > I have a data-validation list box. Is it possible for the source cells for
> >> > the list to be on another sheet from the one in which the list-box
> >> appears?
> >> >
> >> > I know similar questions have been asked before (I poked around before I
> >> > submitted this), and one fellow suggested that a data-validation iist-box
> >> can
> >> > reference another sheet if the list on the other sheet is named. Sadly, I
> >> > don't know how to do this. If cell A1 on Sheet 1, for example, has a list
> >> box
> >> > whose source cells are Sheet 2, A1:A20, how do I assign a name to get the
> >> > validation to work? No matter what I do, I get a pop-up saying that
> >> > validation cannot reference other sheets. Help!
> >>
> >>
> >>
>
>
>

If you use data|Validation, then that dropdown arrow won't appear until you
select the cell.

I just give those cells a nice shaded fill color to distinguish them.

But maybe you could add a shape from the drawing toolbar to make it look ok.

Or you could use a dropdown from the Forms toolbar--but the behavior of the
dropdown is different from the behavior of a cell with data|validation.

If you use the dropdown from the forms toolbar, you can assign a linked cell to
that dropdown.

If I used myList as the list of values and A1 as the linkedcell, I could put
this in B1 to get the value inside the dropdown:

=if(a1="","",index(mylist,a1))



Michael Link wrote:
>
> No, it's definitely not working. The data in the cell with the validation is
> restricted correctly, because it only accepts data that appears in the source
> cells for the list on the other sheet, but no drop-down box appears. (I
> double-checked that "In-Cell Dropdown" was checked on the validation pop-up.)
>
> I'm running this on Excel for Mac--I wonder if that has something to do with
> it.
>
> "Bernie Deitrick" wrote:
>
> > Michael,
> >
> > Make sure you select "List" and check "in-cell dropdown"
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Michael Link" <[email protected]> wrote in message
> > news:[email protected]...
> > > Is there a way to get a drop-down list to actually appear, though? I've named
> > > the source cells, and the cell on the other sheet does accept only data from
> > > those cells (thanks!), but I really need a drop-down box to actually appear.
> > > Is that possible?
> > >
> > > "Max" wrote:
> > >
> > >> Use a named range, say MyList,
> > >> then put as the DV source: =MyList
> > >>
> > >> Debra Dalgleish has good coverage on the steps at her:
> > >> http://www.contextures.com/xlDataVal01.html
> > >>
> > >> --
> > >> Rgds
> > >> Max
> > >> xl 97
> > >> ---
> > >> Singapore, GMT+8
> > >> xdemechanik
> > >> http://savefile.com/projects/236895
> > >> --
> > >> "Michael Link" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> > I have a data-validation list box. Is it possible for the source cells for
> > >> > the list to be on another sheet from the one in which the list-box
> > >> appears?
> > >> >
> > >> > I know similar questions have been asked before (I poked around before I
> > >> > submitted this), and one fellow suggested that a data-validation iist-box
> > >> can
> > >> > reference another sheet if the list on the other sheet is named. Sadly, I
> > >> > don't know how to do this. If cell A1 on Sheet 1, for example, has a list
> > >> box
> > >> > whose source cells are Sheet 2, A1:A20, how do I assign a name to get the
> > >> > validation to work? No matter what I do, I get a pop-up saying that
> > >> > validation cannot reference other sheets. Help!
> > >>
> > >>
> > >>
> >
> >
> >

--

Dave Peterson

Is the cell with data validation in a frozen part of the window. In some
versions of Excel, that prevents the dropdown arrow from showing.

If that's the problem, you could use Window>Split instead.

Michael Link wrote:
> No, it's definitely not working. The data in the cell with the validation is
> restricted correctly, because it only accepts data that appears in the source
> cells for the list on the other sheet, but no drop-down box appears. (I
> double-checked that "In-Cell Dropdown" was checked on the validation pop-up.)
>
> I'm running this on Excel for Mac--I wonder if that has something to do with
> it.
>
> "Bernie Deitrick" wrote:
>
>
>>Michael,
>>
>>Make sure you select "List" and check "in-cell dropdown"
>>
>>HTH,
>>Bernie
>>MS Excel MVP
>>
>>
>>"Michael Link" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>Is there a way to get a drop-down list to actually appear, though? I've named
>>>the source cells, and the cell on the other sheet does accept only data from
>>>those cells (thanks!), but I really need a drop-down box to actually appear.
>>>Is that possible?
>>>
>>>"Max" wrote:
>>>
>>>
>>>>Use a named range, say MyList,
>>>>then put as the DV source: =MyList
>>>>
>>>>Debra Dalgleish has good coverage on the steps at her:
>>>>http://www.contextures.com/xlDataVal01.html
>>>>
>>>>--
>>>>Rgds
>>>>Max
>>>>xl 97
>>>>---
>>>>Singapore, GMT+8
>>>>xdemechanik
>>>>http://savefile.com/projects/236895
>>>>--
>>>>"Michael Link" <[email protected]> wrote in message
>>>>news:[email protected]...
>>>>
>>>>>I have a data-validation list box. Is it possible for the source cells for
>>>>>the list to be on another sheet from the one in which the list-box
>>>>
>>>>appears?
>>>>
>>>>> I know similar questions have been asked before (I poked around before I
>>>>>submitted this), and one fellow suggested that a data-validation iist-box
>>>>
>>>>can
>>>>
>>>>>reference another sheet if the list on the other sheet is named. Sadly, I
>>>>>don't know how to do this. If cell A1 on Sheet 1, for example, has a list
>>>>
>>>>box
>>>>
>>>>>whose source cells are Sheet 2, A1:A20, how do I assign a name to get the
>>>>>validation to work? No matter what I do, I get a pop-up saying that
>>>>>validation cannot reference other sheets. Help!
>>>>
>>>>
>>>>
>>
>>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Ahhhh.

I see.

Debra Dalgleish wrote:
>
> Is the cell with data validation in a frozen part of the window. In some
> versions of Excel, that prevents the dropdown arrow from showing.
>
> If that's the problem, you could use Window>Split instead.
>
> Michael Link wrote:
> > No, it's definitely not working. The data in the cell with the validation is
> > restricted correctly, because it only accepts data that appears in the source
> > cells for the list on the other sheet, but no drop-down box appears. (I
> > double-checked that "In-Cell Dropdown" was checked on the validation pop-up.)
> >
> > I'm running this on Excel for Mac--I wonder if that has something to do with
> > it.
> >
> > "Bernie Deitrick" wrote:
> >
> >
> >>Michael,
> >>
> >>Make sure you select "List" and check "in-cell dropdown"
> >>
> >>HTH,
> >>Bernie
> >>MS Excel MVP
> >>
> >>
> >>"Michael Link" <[email protected]> wrote in message
> >>news:[email protected]...
> >>
> >>>Is there a way to get a drop-down list to actually appear, though? I've named
> >>>the source cells, and the cell on the other sheet does accept only data from
> >>>those cells (thanks!), but I really need a drop-down box to actually appear.
> >>>Is that possible?
> >>>
> >>>"Max" wrote:
> >>>
> >>>
> >>>>Use a named range, say MyList,
> >>>>then put as the DV source: =MyList
> >>>>
> >>>>Debra Dalgleish has good coverage on the steps at her:
> >>>>http://www.contextures.com/xlDataVal01.html
> >>>>
> >>>>--
> >>>>Rgds
> >>>>Max
> >>>>xl 97
> >>>>---
> >>>>Singapore, GMT+8
> >>>>xdemechanik
> >>>>http://savefile.com/projects/236895
> >>>>--
> >>>>"Michael Link" <[email protected]> wrote in message
> >>>>news:[email protected]...
> >>>>
> >>>>>I have a data-validation list box. Is it possible for the source cells for
> >>>>>the list to be on another sheet from the one in which the list-box
> >>>>
> >>>>appears?
> >>>>
> >>>>> I know similar questions have been asked before (I poked around before I
> >>>>>submitted this), and one fellow suggested that a data-validation iist-box
> >>>>
> >>>>can
> >>>>
> >>>>>reference another sheet if the list on the other sheet is named. Sadly, I
> >>>>>don't know how to do this. If cell A1 on Sheet 1, for example, has a list
> >>>>
> >>>>box
> >>>>
> >>>>>whose source cells are Sheet 2, A1:A20, how do I assign a name to get the
> >>>>>validation to work? No matter what I do, I get a pop-up saying that
> >>>>>validation cannot reference other sheets. Help!
> >>>>
> >>>>
> >>>>
> >>
> >>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html

--

Dave Peterson

Well, I'm just guessing!

Dave Peterson wrote:
> Ahhhh.
>
> I see.
>
> Debra Dalgleish wrote:
>
>>Is the cell with data validation in a frozen part of the window. In some
>>versions of Excel, that prevents the dropdown arrow from showing.
>>
>>If that's the problem, you could use Window>Split instead.
>>
>>Michael Link wrote:
>>
>>>No, it's definitely not working. The data in the cell with the validation is
>>>restricted correctly, because it only accepts data that appears in the source
>>>cells for the list on the other sheet, but no drop-down box appears. (I
>>>double-checked that "In-Cell Dropdown" was checked on the validation pop-up.)
>>>
>>>I'm running this on Excel for Mac--I wonder if that has something to do with
>>>it.
>>>
>>>"Bernie Deitrick" wrote:
>>>
>>>
>>>
>>>>Michael,
>>>>
>>>>Make sure you select "List" and check "in-cell dropdown"
>>>>
>>>>HTH,
>>>>Bernie
>>>>MS Excel MVP
>>>>
>>>>
>>>>"Michael Link" <[email protected]> wrote in message
>>>>news:[email protected]...
>>>>
>>>>
>>>>>Is there a way to get a drop-down list to actually appear, though? I've named
>>>>>the source cells, and the cell on the other sheet does accept only data from
>>>>>those cells (thanks!), but I really need a drop-down box to actually appear.
>>>>>Is that possible?
>>>>>
>>>>>"Max" wrote:
>>>>>
>>>>>
>>>>>
>>>>>>Use a named range, say MyList,
>>>>>>then put as the DV source: =MyList
>>>>>>
>>>>>>Debra Dalgleish has good coverage on the steps at her:
>>>>>>http://www.contextures.com/xlDataVal01.html
>>>>>>
>>>>>>--
>>>>>>Rgds
>>>>>>Max
>>>>>>xl 97
>>>>>>---
>>>>>>Singapore, GMT+8
>>>>>>xdemechanik
>>>>>>http://savefile.com/projects/236895
>>>>>>--
>>>>>>"Michael Link" <[email protected]> wrote in message
>>>>>>news:[email protected]...
>>>>>>
>>>>>>
>>>>>>>I have a data-validation list box. Is it possible for the source cells for
>>>>>>>the list to be on another sheet from the one in which the list-box
>>>>>>
>>>>>>appears?
>>>>>>
>>>>>>
>>>>>>>I know similar questions have been asked before (I poked around before I
>>>>>>>submitted this), and one fellow suggested that a data-validation iist-box
>>>>>>
>>>>>>can
>>>>>>
>>>>>>
>>>>>>>reference another sheet if the list on the other sheet is named. Sadly, I
>>>>>>>don't know how to do this. If cell A1 on Sheet 1, for example, has a list
>>>>>>
>>>>>>box
>>>>>>
>>>>>>
>>>>>>>whose source cells are Sheet 2, A1:A20, how do I assign a name to get the
>>>>>>>validation to work? No matter what I do, I get a pop-up saying that
>>>>>>>validation cannot reference other sheets. Help!
>>>>>>
>>>>>>
>>>>>>
>>>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

"Michael Link" wrote:
> .. I'm running this on Excel for Mac--
> I wonder if that has something to do with it.

Debra / Dave,

I don't know about the OP's line above (never had a Mac)
Any ideas?

That aside, I'm not sure, but what the OP described about " .. no drop-down
box appears .. " does strike a familiar chord with a past incidence
experienced (re discussions with Dave in: http://tinyurl.com/cxjpe ) where
all the DV dropdowns just plain disappeared on one sheet one fine day, and
all revival attempts were futile. And attempts to create new DVs in the
sheet didn't work either (no dropdowns).
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Now we have three interpretations!

<vbg>

Max wrote:
>
> "Michael Link" wrote:
> > .. I'm running this on Excel for Mac--
> > I wonder if that has something to do with it.
>
> Debra / Dave,
>
> I don't know about the OP's line above (never had a Mac)
> Any ideas?
>
> That aside, I'm not sure, but what the OP described about " .. no drop-down
> box appears .. " does strike a familiar chord with a past incidence
> experienced (re discussions with Dave in: http://tinyurl.com/cxjpe ) where
> all the DV dropdowns just plain disappeared on one sheet one fine day, and
> all revival attempts were futile. And attempts to create new DVs in the
> sheet didn't work either (no dropdowns).
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --

--

Dave Peterson