Thread: 3 way dependant lists

If i have 2 drop down lists, where by selecting the first it narrows down the
options in the second list to those that are applicable to the chosen choice.
is it possible and if so how do i then link this second selected option to a
third drop down box?

for example, by choosing a login in the first box it then shortlists
relevant domains and finally i want to link the appropriate user names that
are relevant to the choice in the second box (iv used named ranges). the
first 2 formulas i used for the first 2 boxes that works well, in
data/validation respectively were:

=IF(D5="",DealerIDList,INDEX(DealerIDColumn,MATCH(D5,LoginColumn,0)))

=OFFSET(DealerIDStart,MATCH(B5,DealerIDColumn,0)-1,1,COUNTIF(DealerIDColumn,B5),1)

Much appreciated

Paul

Just use a similar technique for the 3rd list as the 2nd list, using the
value from the second list.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"stratmyster" <stratmyster@discussions.microsoft.com> wrote in message
news:286BC842-6B61-4E6A-854E-A9A96F764DDC@microsoft.com...
> If i have 2 drop down lists, where by selecting the first it narrows down
the
> options in the second list to those that are applicable to the chosen
choice.
> is it possible and if so how do i then link this second selected option to
a
> third drop down box?
>
> for example, by choosing a login in the first box it then shortlists
> relevant domains and finally i want to link the appropriate user names
that
> are relevant to the choice in the second box (iv used named ranges). the
> first 2 formulas i used for the first 2 boxes that works well, in
> data/validation respectively were:
>
> =IF(D5="",DealerIDList,INDEX(DealerIDColumn,MATCH(D5,LoginColumn,0)))
>
>
=OFFSET(DealerIDStart,MATCH(B5,DealerIDColumn,0)-1,1,COUNTIF(DealerIDColumn,
B5),1)
>
> Much appreciated
>
> Paul
>
>

the only problem is. if i have already got:

=OFFSET(DealerIDStart,MATCH(B5,DealerIDColumn,0)-1,1,COUNTIF(DealerIDColumn,
B5),1)

in the second cells data validation input line, how do i then add the first
formula:

=IF(D5="",DealerIDList,INDEX(DealerIDColumn,MATCH(D5,LoginColumn,0)))

into that same space? because i need both formulas in their respective cells
for it to work, but i dont undertstand how to incorporate them! (i no that
the cell references would be different and the cell ranges would be named
differently!)

iv been looking at: http://www.contextures.com/xlDataVal13.html as a starter
so you know what im talking about!

thanks!x

"Bob Phillips" wrote:

> Just use a similar technique for the 3rd list as the 2nd list, using the
> value from the second list.
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "stratmyster" <stratmyster@discussions.microsoft.com> wrote in message
> news:286BC842-6B61-4E6A-854E-A9A96F764DDC@microsoft.com...
> > If i have 2 drop down lists, where by selecting the first it narrows down
> the
> > options in the second list to those that are applicable to the chosen
> choice.
> > is it possible and if so how do i then link this second selected option to
> a
> > third drop down box?
> >
> > for example, by choosing a login in the first box it then shortlists
> > relevant domains and finally i want to link the appropriate user names
> that
> > are relevant to the choice in the second box (iv used named ranges). the
> > first 2 formulas i used for the first 2 boxes that works well, in
> > data/validation respectively were:
> >
> > =IF(D5="",DealerIDList,INDEX(DealerIDColumn,MATCH(D5,LoginColumn,0)))
> >
> >
> =OFFSET(DealerIDStart,MATCH(B5,DealerIDColumn,0)-1,1,COUNTIF(DealerIDColumn,
> B5),1)
> >
> > Much appreciated
> >
> > Paul
> >
> >
>
>
>

Why would you need both? Whenever I have implemented this, I add a simple
list in DV1, then an INDEXed list in DV2, DV3 etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"stratmyster" <stratmyster@discussions.microsoft.com> wrote in message
news:B62B2432-3A9F-4BF8-9E3B-A353EFFC905A@microsoft.com...
> the only problem is. if i have already got:
>
>
=OFFSET(DealerIDStart,MATCH(B5,DealerIDColumn,0)-1,1,COUNTIF(DealerIDColumn,
> B5),1)
>
> in the second cells data validation input line, how do i then add the
first
> formula:
>
> =IF(D5="",DealerIDList,INDEX(DealerIDColumn,MATCH(D5,LoginColumn,0)))
>
> into that same space? because i need both formulas in their respective
cells
> for it to work, but i dont undertstand how to incorporate them! (i no that
> the cell references would be different and the cell ranges would be named
> differently!)
>
> iv been looking at: http://www.contextures.com/xlDataVal13.html as a
starter
> so you know what im talking about!
>
> thanks!x
>
> "Bob Phillips" wrote:
>
> > Just use a similar technique for the 3rd list as the 2nd list, using the
> > value from the second list.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "stratmyster" <stratmyster@discussions.microsoft.com> wrote in message
> > news:286BC842-6B61-4E6A-854E-A9A96F764DDC@microsoft.com...
> > > If i have 2 drop down lists, where by selecting the first it narrows
down
> > the
> > > options in the second list to those that are applicable to the chosen
> > choice.
> > > is it possible and if so how do i then link this second selected
option to
> > a
> > > third drop down box?
> > >
> > > for example, by choosing a login in the first box it then shortlists
> > > relevant domains and finally i want to link the appropriate user names
> > that
> > > are relevant to the choice in the second box (iv used named ranges).
the
> > > first 2 formulas i used for the first 2 boxes that works well, in
> > > data/validation respectively were:
> > >
> > > =IF(D5="",DealerIDList,INDEX(DealerIDColumn,MATCH(D5,LoginColumn,0)))
> > >
> > >
> >
=OFFSET(DealerIDStart,MATCH(B5,DealerIDColumn,0)-1,1,COUNTIF(DealerIDColumn,
> > B5),1)
> > >
> > > Much appreciated
> > >
> > > Paul
> > >
> > >
> >
> >
> >