conditional data validation

I'm trying to create a conditional data validation for a cell ($D4) based on
the entry in cell ($B4). There are three possible entries for cell $B4.
I've created a named list for $B4 and based on the entry I want three
different conditional data validation types for cell $D4:

1. Allow a range of numbers (1 to 255)
2. Drop-down list A
3. Drop-down list B

I'm fairly proficient with Excel; however, I'm a very novice VBA programmer.

Do you have any suggestions on the above situation?

Rick

Rick,

Try this formula in the List Allow type in DV

=IF(B4=1,ROW(1:255),IF(B4=2,listA,listB))

assuming that the two lists are named, else use the range references.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"RickS" <RickS@discussions.microsoft.com> wrote in message
news:A7D6B166-64A7-4085-AA00-E4975230A53E@microsoft.com...
> I'm trying to create a conditional data validation for a cell ($D4) based
on
> the entry in cell ($B4). There are three possible entries for cell $B4.
> I've created a named list for $B4 and based on the entry I want three
> different conditional data validation types for cell $D4:
>
> 1. Allow a range of numbers (1 to 255)
> 2. Drop-down list A
> 3. Drop-down list B
>
> I'm fairly proficient with Excel; however, I'm a very novice VBA
programmer.
>
> Do you have any suggestions on the above situation?
>
> Rick
>

Bob,

I tried your suggest and it does the provide the correct data validation;
however, I was hoping that cell $D4 would actually display the appropriate
drop-down list for user to choose from. I believe I can combine your
suggestion with a procedure for using Dynamic lists that I found out on the
Contextures web site. Thank you for your help.

RickS

"Bob Phillips" wrote:

> Rick,
>
> Try this formula in the List Allow type in DV
>
> =IF(B4=1,ROW(1:255),IF(B4=2,listA,listB))
>
> assuming that the two lists are named, else use the range references.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "RickS" <RickS@discussions.microsoft.com> wrote in message
> news:A7D6B166-64A7-4085-AA00-E4975230A53E@microsoft.com...
> > I'm trying to create a conditional data validation for a cell ($D4) based
> on
> > the entry in cell ($B4). There are three possible entries for cell $B4.
> > I've created a named list for $B4 and based on the entry I want three
> > different conditional data validation types for cell $D4:
> >
> > 1. Allow a range of numbers (1 to 255)
> > 2. Drop-down list A
> > 3. Drop-down list B
> >
> > I'm fairly proficient with Excel; however, I'm a very novice VBA
> programmer.
> >
> > Do you have any suggestions on the above situation?
> >
> > Rick
> >
>
>
>

Rick,

In my tests listA and listB will shows as DD, it is only the 1-255 that
doesn't. Isn't that what happens with you, or am I misunderstanding what you
want?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"RickS" <RickS@discussions.microsoft.com> wrote in message
news:5D365249-C4CE-4F7F-834B-46695293BD18@microsoft.com...
> Bob,
>
> I tried your suggest and it does the provide the correct data validation;
> however, I was hoping that cell $D4 would actually display the appropriate
> drop-down list for user to choose from. I believe I can combine your
> suggestion with a procedure for using Dynamic lists that I found out on
the
> Contextures web site. Thank you for your help.
>
> RickS
>
> "Bob Phillips" wrote:
>
> > Rick,
> >
> > Try this formula in the List Allow type in DV
> >
> > =IF(B4=1,ROW(1:255),IF(B4=2,listA,listB))
> >
> > assuming that the two lists are named, else use the range references.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "RickS" <RickS@discussions.microsoft.com> wrote in message
> > news:A7D6B166-64A7-4085-AA00-E4975230A53E@microsoft.com...
> > > I'm trying to create a conditional data validation for a cell ($D4)
based
> > on
> > > the entry in cell ($B4). There are three possible entries for cell
$B4.
> > > I've created a named list for $B4 and based on the entry I want three
> > > different conditional data validation types for cell $D4:
> > >
> > > 1. Allow a range of numbers (1 to 255)
> > > 2. Drop-down list A
> > > 3. Drop-down list B
> > >
> > > I'm fairly proficient with Excel; however, I'm a very novice VBA
> > programmer.
> > >
> > > Do you have any suggestions on the above situation?
> > >
> > > Rick
> > >
> >
> >
> >

Bob,

My DDs are not showing. I knew the 1-255 wouldn't show; however, I was
going to handle this via the validation input message. You do understand
what I'm trying to do, I'm just having problems with the execution. I'm
going to play wiht this some more and I'll let you know how I make out.
Thanks.

Rick

"Bob Phillips" wrote:

> Rick,
>
> In my tests listA and listB will shows as DD, it is only the 1-255 that
> doesn't. Isn't that what happens with you, or am I misunderstanding what you
> want?
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "RickS" <RickS@discussions.microsoft.com> wrote in message
> news:5D365249-C4CE-4F7F-834B-46695293BD18@microsoft.com...
> > Bob,
> >
> > I tried your suggest and it does the provide the correct data validation;
> > however, I was hoping that cell $D4 would actually display the appropriate
> > drop-down list for user to choose from. I believe I can combine your
> > suggestion with a procedure for using Dynamic lists that I found out on
> the
> > Contextures web site. Thank you for your help.
> >
> > RickS
> >
> > "Bob Phillips" wrote:
> >
> > > Rick,
> > >
> > > Try this formula in the List Allow type in DV
> > >
> > > =IF(B4=1,ROW(1:255),IF(B4=2,listA,listB))
> > >
> > > assuming that the two lists are named, else use the range references.
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "RickS" <RickS@discussions.microsoft.com> wrote in message
> > > news:A7D6B166-64A7-4085-AA00-E4975230A53E@microsoft.com...
> > > > I'm trying to create a conditional data validation for a cell ($D4)
> based
> > > on
> > > > the entry in cell ($B4). There are three possible entries for cell
> $B4.
> > > > I've created a named list for $B4 and based on the entry I want three
> > > > different conditional data validation types for cell $D4:
> > > >
> > > > 1. Allow a range of numbers (1 to 255)
> > > > 2. Drop-down list A
> > > > 3. Drop-down list B
> > > >
> > > > I'm fairly proficient with Excel; however, I'm a very novice VBA
> > > programmer.
> > > >
> > > > Do you have any suggestions on the above situation?
> > > >
> > > > Rick
> > > >
> > >
> > >
> > >
>
>
>

Rick,

I have posted a simple example at http://cjoint.com/?mqoiKusXdS

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"RickS" <RickS@discussions.microsoft.com> wrote in message
news:093D87CB-0346-4DA9-9F78-FC1153F53653@microsoft.com...
> Bob,
>
> My DDs are not showing. I knew the 1-255 wouldn't show; however, I was
> going to handle this via the validation input message. You do understand
> what I'm trying to do, I'm just having problems with the execution. I'm
> going to play wiht this some more and I'll let you know how I make out.
> Thanks.
>
> Rick
>
> "Bob Phillips" wrote:
>
> > Rick,
> >
> > In my tests listA and listB will shows as DD, it is only the 1-255 that
> > doesn't. Isn't that what happens with you, or am I misunderstanding what
you
> > want?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "RickS" <RickS@discussions.microsoft.com> wrote in message
> > news:5D365249-C4CE-4F7F-834B-46695293BD18@microsoft.com...
> > > Bob,
> > >
> > > I tried your suggest and it does the provide the correct data
validation;
> > > however, I was hoping that cell $D4 would actually display the
appropriate
> > > drop-down list for user to choose from. I believe I can combine your
> > > suggestion with a procedure for using Dynamic lists that I found out
on
> > the
> > > Contextures web site. Thank you for your help.
> > >
> > > RickS
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Rick,
> > > >
> > > > Try this formula in the List Allow type in DV
> > > >
> > > > =IF(B4=1,ROW(1:255),IF(B4=2,listA,listB))
> > > >
> > > > assuming that the two lists are named, else use the range
references.
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove nothere from email address if mailing direct)
> > > >
> > > > "RickS" <RickS@discussions.microsoft.com> wrote in message
> > > > news:A7D6B166-64A7-4085-AA00-E4975230A53E@microsoft.com...
> > > > > I'm trying to create a conditional data validation for a cell
($D4)
> > based
> > > > on
> > > > > the entry in cell ($B4). There are three possible entries for
cell
> > $B4.
> > > > > I've created a named list for $B4 and based on the entry I want
three
> > > > > different conditional data validation types for cell $D4:
> > > > >
> > > > > 1. Allow a range of numbers (1 to 255)
> > > > > 2. Drop-down list A
> > > > > 3. Drop-down list B
> > > > >
> > > > > I'm fairly proficient with Excel; however, I'm a very novice VBA
> > > > programmer.
> > > > >
> > > > > Do you have any suggestions on the above situation?
> > > > >
> > > > > Rick
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Bob,

Just determined what I was doing wrong. Your suggestion works great.
Thanks for the help.

Rick

"RickS" wrote:

> Bob,
>
> My DDs are not showing. I knew the 1-255 wouldn't show; however, I was
> going to handle this via the validation input message. You do understand
> what I'm trying to do, I'm just having problems with the execution. I'm
> going to play wiht this some more and I'll let you know how I make out.
> Thanks.
>
> Rick
>
> "Bob Phillips" wrote:
>
> > Rick,
> >
> > In my tests listA and listB will shows as DD, it is only the 1-255 that
> > doesn't. Isn't that what happens with you, or am I misunderstanding what you
> > want?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "RickS" <RickS@discussions.microsoft.com> wrote in message
> > news:5D365249-C4CE-4F7F-834B-46695293BD18@microsoft.com...
> > > Bob,
> > >
> > > I tried your suggest and it does the provide the correct data validation;
> > > however, I was hoping that cell $D4 would actually display the appropriate
> > > drop-down list for user to choose from. I believe I can combine your
> > > suggestion with a procedure for using Dynamic lists that I found out on
> > the
> > > Contextures web site. Thank you for your help.
> > >
> > > RickS
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Rick,
> > > >
> > > > Try this formula in the List Allow type in DV
> > > >
> > > > =IF(B4=1,ROW(1:255),IF(B4=2,listA,listB))
> > > >
> > > > assuming that the two lists are named, else use the range references.
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove nothere from email address if mailing direct)
> > > >
> > > > "RickS" <RickS@discussions.microsoft.com> wrote in message
> > > > news:A7D6B166-64A7-4085-AA00-E4975230A53E@microsoft.com...
> > > > > I'm trying to create a conditional data validation for a cell ($D4)
> > based
> > > > on
> > > > > the entry in cell ($B4). There are three possible entries for cell
> > $B4.
> > > > > I've created a named list for $B4 and based on the entry I want three
> > > > > different conditional data validation types for cell $D4:
> > > > >
> > > > > 1. Allow a range of numbers (1 to 255)
> > > > > 2. Drop-down list A
> > > > > 3. Drop-down list B
> > > > >
> > > > > I'm fairly proficient with Excel; however, I'm a very novice VBA
> > > > programmer.
> > > > >
> > > > > Do you have any suggestions on the above situation?
> > > > >
> > > > > Rick
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >