I want to create a cell data validation input based on a list but make it
conditional on a yes/no selection

Example, two columns :

Col A Col B
-------- ------------
Yes Prod x
No Item a
No Prod f
Yes Item g
No item b

The above lists are variable and cannot be sorted, but have assigned range
names, rngYesNo = a1:a5, rngList = b1:b5.

What I am trying to do is create a cell data validation elsewhere in
worksheet which will only show the items in rngList which have a Yes in
column A ; so in the above example it would only show Prod x and Item g.

thanks,
--
Regards & Thanks

Debra Dalgleish shows how to create dependent lists:
http://contextures.com/xlDataVal02.html

Ian wrote:
>
> I want to create a cell data validation input based on a list but make it
> conditional on a yes/no selection
>
> Example, two columns :
>
> Col A Col B
> -------- ------------
> Yes Prod x
> No Item a
> No Prod f
> Yes Item g
> No item b
>
> The above lists are variable and cannot be sorted, but have assigned range
> names, rngYesNo = a1:a5, rngList = b1:b5.
>
> What I am trying to do is create a cell data validation elsewhere in
> worksheet which will only show the items in rngList which have a Yes in
> column A ; so in the above example it would only show Prod x and Item g.
>
> thanks,
> --
> Regards & Thanks

--

Dave Peterson

On Mar 23, 8:39 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Debra Dalgleish shows how to create dependent lists:http://contextures.com/xlDataVal02.html
>
>
>
>
>
> Ian wrote:
>
> > I want to create a cell data validation input based on a list but make it
> > conditional on a yes/no selection
>
> > Example, two columns :
>
> > Col A Col B
> > -------- ------------
> > Yes Prod x
> > No Item a
> > No Prod f
> > Yes Item g
> > No item b
>
> > The above lists are variable and cannot be sorted, but have assigned range
> > names, rngYesNo = a1:a5, rngList = b1:b5.
>
> > What I am trying to do is create a cell data validation elsewhere in
> > worksheet which will only show the items in rngList which have a Yes in
> > column A ; so in the above example it would only show Prod x and Item g.
>
> > thanks,
> > --
> > Regards & Thanks
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Dave, I looked at this, and from what I can tell, what Ian's asking
for cannot be found on Debra's site. I'm not sure it can be done
without some changes to the worksheet first. VLookup can't be used as
the list isn't sorted.

You're right. A couple more named lists would need to be created.

One with just yes and no
One with the options related to yes
one with the options related to no

But the original lists don't have to change (that's a stretch, huh? <bg>).

okrob wrote:
>
> On Mar 23, 8:39 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > Debra Dalgleish shows how to create dependent lists:http://contextures.com/xlDataVal02.html
> >
> >
> >
> >
> >
> > Ian wrote:
> >
> > > I want to create a cell data validation input based on a list but make it
> > > conditional on a yes/no selection
> >
> > > Example, two columns :
> >
> > > Col A Col B
> > > -------- ------------
> > > Yes Prod x
> > > No Item a
> > > No Prod f
> > > Yes Item g
> > > No item b
> >
> > > The above lists are variable and cannot be sorted, but have assigned range
> > > names, rngYesNo = a1:a5, rngList = b1:b5.
> >
> > > What I am trying to do is create a cell data validation elsewhere in
> > > worksheet which will only show the items in rngList which have a Yes in
> > > column A ; so in the above example it would only show Prod x and Item g.
> >
> > > thanks,
> > > --
> > > Regards & Thanks
> >
> > --
> >
> > Dave Peterson- Hide quoted text -
> >
> > - Show quoted text -
>
> Dave, I looked at this, and from what I can tell, what Ian's asking
> for cannot be found on Debra's site. I'm not sure it can be done
> without some changes to the worksheet first. VLookup can't be used as
> the list isn't sorted.

--

Dave Peterson

On Mar 23, 9:57 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> You're right. A couple more named lists would need to be created.
>
> One with just yes and no
> One with the options related to yes
> one with the options related to no
>
> But the original lists don't have to change (that's a stretch, huh? <bg>).
>
>
>
>
>
> okrob wrote:
>
> > On Mar 23, 8:39 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > Debra Dalgleish shows how to create dependent lists:http://contextures.com/xlDataVal02.html
>
> > > Ian wrote:
>
> > > > I want to create a cell data validation input based on a list but make it
> > > > conditional on a yes/no selection
>
> > > > Example, two columns :
>
> > > > Col A Col B
> > > > -------- ------------
> > > > Yes Prod x
> > > > No Item a
> > > > No Prod f
> > > > Yes Item g
> > > > No item b
>
> > > > The above lists are variable and cannot be sorted, but have assigned range
> > > > names, rngYesNo = a1:a5, rngList = b1:b5.
>
> > > > What I am trying to do is create a cell data validation elsewhere in
> > > > worksheet which will only show the items in rngList which have a Yes in
> > > > column A ; so in the above example it would only show Prod x and Item g.
>
> > > > thanks,
> > > > --
> > > > Regards & Thanks
>
> > > --
>
> > > Dave Peterson- Hide quoted text -
>
> > > - Show quoted text -
>
> > Dave, I looked at this, and from what I can tell, what Ian's asking
> > for cannot be found on Debra's site. I'm not sure it can be done
> > without some changes to the worksheet first. VLookup can't be used as
> > the list isn't sorted.
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Ok... I see where you're going. It's not so much a stetch, but it
would be quite involved if the original list isn't a static list.

You could create lists from formulas, as in the sample file here:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0048 - Dynamic Dependent Dropdowns
from Unsorted List'

Ian wrote:
> I want to create a cell data validation input based on a list but make it
> conditional on a yes/no selection
>
> Example, two columns :
>
> Col A Col B
> -------- ------------
> Yes Prod x
> No Item a
> No Prod f
> Yes Item g
> No item b
>
> The above lists are variable and cannot be sorted, but have assigned range
> names, rngYesNo = a1:a5, rngList = b1:b5.
>
> What I am trying to do is create a cell data validation elsewhere in
> worksheet which will only show the items in rngList which have a Yes in
> column A ; so in the above example it would only show Prod x and Item g.
>
> thanks,


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Many thanks... managed to get this to work based on the example suggested.

--
Regards & Thanks


"Debra Dalgleish" wrote:

> You could create lists from formulas, as in the sample file here:
>
> http://www.contextures.com/excelfiles.html
>
> Under Data Validation, look for 'DV0048 - Dynamic Dependent Dropdowns
> from Unsorted List'
>
> Ian wrote:
> > I want to create a cell data validation input based on a list but make it
> > conditional on a yes/no selection
> >
> > Example, two columns :
> >
> > Col A Col B
> > -------- ------------
> > Yes Prod x
> > No Item a
> > No Prod f
> > Yes Item g
> > No item b
> >
> > The above lists are variable and cannot be sorted, but have assigned range
> > names, rngYesNo = a1:a5, rngList = b1:b5.
> >
> > What I am trying to do is create a cell data validation elsewhere in
> > worksheet which will only show the items in rngList which have a Yes in
> > column A ; so in the above example it would only show Prod x and Item g.
> >
> > thanks,
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html
>
>

You're welcome. Thanks for letting me know that it helped.

Ian wrote:
> Many thanks... managed to get this to work based on the example suggested.
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html