Data Valididation using Dependent lists using Offset()

Hello,
I have a dependent list of 4 levels: Dept, Office, Division, Branch.
Each selection starting with department will provide lists based on
the previous selection. This is perfectly straightforward and
functions flawlessly. I am using named ranges and then using the
Offset function to obtain the proper list.

I would like to restrict options to a valid list in every
circumstance. My problem arises if there is no data in a previous
level and the user decides to type a value in where there is no list.
For example, level 1 (Dept) must be filled in with only the data --
that works fine in the validation sequence. However, if level 1 is
blank, the user can type anything in level 2 (office) and it is
accepted. Level 3 dropdown list will fail since it is dependent on
level 2. Not all entries have 4 levels; some only 3 levels.

Again, this all works fine if the user starts at level 1 then goes in
the proper sequence.

Any thoughts appreciated.

Bob
Tue Dec 04 10:27:14 PST 2007

Untick the Ignore Blank option.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"bluegrassstateworker" <andy.crumbacker@gmail.com> wrote in message
news:bc08be74-b150-4b73-ab81-c34dfc941f83@r60g2000hsc.googlegroups.com...
> Hello,
> I have a dependent list of 4 levels: Dept, Office, Division, Branch.
> Each selection starting with department will provide lists based on
> the previous selection. This is perfectly straightforward and
> functions flawlessly. I am using named ranges and then using the
> Offset function to obtain the proper list.
>
> I would like to restrict options to a valid list in every
> circumstance. My problem arises if there is no data in a previous
> level and the user decides to type a value in where there is no list.
> For example, level 1 (Dept) must be filled in with only the data --
> that works fine in the validation sequence. However, if level 1 is
> blank, the user can type anything in level 2 (office) and it is
> accepted. Level 3 dropdown list will fail since it is dependent on
> level 2. Not all entries have 4 levels; some only 3 levels.
>
> Again, this all works fine if the user starts at level 1 then goes in
> the proper sequence.
>
> Any thoughts appreciated.

bluegrassstateworker
Wed Dec 05 07:30:45 PST 2007

On Dec 4, 1:27 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> Untick the Ignore Blank option.
>
> --
> ---
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> "bluegrassstateworker" <andy.crumbac...@gmail.com> wrote in message
>
> news:bc08be74-b150-4b73-ab81-c34dfc941f83@r60g2000hsc.googlegroups.com...
>
>
>
> > Hello,
> > I have a dependent list of 4 levels: Dept, Office, Division, Branch.
> > Each selection starting with department will provide lists based on
> > the previous selection. This is perfectly straightforward and
> > functions flawlessly. I am using named ranges and then using the
> > Offset function to obtain the proper list.
>
> > I would like to restrict options to a valid list in every
> > circumstance. My problem arises if there is no data in a previous
> > level and the user decides to type a value in where there is no list.
> > For example, level 1 (Dept) must be filled in with only the data --
> > that works fine in the validation sequence. However, if level 1 is
> > blank, the user can type anything in level 2 (office) and it is
> > accepted. Level 3 dropdown list will fail since it is dependent on
> > level 2. Not all entries have 4 levels; some only 3 levels.
>
> > Again, this all works fine if the user starts at level 1 then goes in
> > the proper sequence.
>
> > Any thoughts appreciated.- Hide quoted text -
>
> - Show quoted text -

That did it. Thanks.