Need a conditional drop-down list (list contents)

I am trying to limit the number of data entry errors from users.
In cell F2, I have a drop-down box (data|validation|list) with the valid
options.
In cell D19, I need to have another drop-down box with a list of all valid
options - based on the selection from cell F2. I have tables in the
worksheet that provide the locations of the contingent data as both named
ranges and exact matrix references.

I have tried using formulas using IF and LOOKUP commands but the results I
get (either a named range or the exact range depending on which I decide to
use) are unusable for (data|validation|list). I am trying to use VBA's
AutoFilter but am having trouble with it (I'm new to VBA, so no surprise
there). Illustration may help clarify my ordeal

Entry:
Products F2 drop down box
Options D19 drop-down box based on selection in cell F2.

Tables:

Product 1: PECB
PECB Options List found in range AA5:AA10 (range called "OPTIONS-A")

Product 2: GLUB
GLUB Options List found in range AB5:AB15 (range called "OPTIONS-B")

How do I get the range result from a Lookup formula or VBA's AutoFilter to
show the values within the range?

If I am understanding you correctly, create range names for each of the
option lists of the product for those options. So range AA5:AA10 would be
named PECB, etc.

Then in the DV for the options use an allow type of list with a formula of
=INDIRECT(product_list_cell)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"S&F Tie Guy" <S&F Tie [email protected]> wrote in message
news:[email protected]...
> I am trying to limit the number of data entry errors from users.
> In cell F2, I have a drop-down box (data|validation|list) with the valid
> options.
> In cell D19, I need to have another drop-down box with a list of all valid
> options - based on the selection from cell F2. I have tables in the
> worksheet that provide the locations of the contingent data as both named
> ranges and exact matrix references.
>
> I have tried using formulas using IF and LOOKUP commands but the results I
> get (either a named range or the exact range depending on which I decide
to
> use) are unusable for (data|validation|list). I am trying to use VBA's
> AutoFilter but am having trouble with it (I'm new to VBA, so no surprise
> there). Illustration may help clarify my ordeal
>
> Entry:
> Products F2 drop down box
> Options D19 drop-down box based on selection in cell F2.
>
> Tables:
>
> Product 1: PECB
> PECB Options List found in range AA5:AA10 (range called "OPTIONS-A")
>
> Product 2: GLUB
> GLUB Options List found in range AB5:AB15 (range called "OPTIONS-B")
>
> How do I get the range result from a Lookup formula or VBA's AutoFilter to
> show the values within the range?

Thank you Bob,
That worked beautifully! I've never used that particular function and noone
here seemed to know it could be used as part of a DV list.

Much appreciated.
S&F Tie Guy
(Steve)

"Bob Phillips" wrote:

> If I am understanding you correctly, create range names for each of the
> option lists of the product for those options. So range AA5:AA10 would be
> named PECB, etc.
>
> Then in the DV for the options use an allow type of list with a formula of
> =INDIRECT(product_list_cell)
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "S&F Tie Guy" <S&F Tie [email protected]> wrote in message
> news:[email protected]...
> > I am trying to limit the number of data entry errors from users.
> > In cell F2, I have a drop-down box (data|validation|list) with the valid
> > options.
> > In cell D19, I need to have another drop-down box with a list of all valid
> > options - based on the selection from cell F2. I have tables in the
> > worksheet that provide the locations of the contingent data as both named
> > ranges and exact matrix references.
> >
> > I have tried using formulas using IF and LOOKUP commands but the results I
> > get (either a named range or the exact range depending on which I decide
> to
> > use) are unusable for (data|validation|list). I am trying to use VBA's
> > AutoFilter but am having trouble with it (I'm new to VBA, so no surprise
> > there). Illustration may help clarify my ordeal
> >
> > Entry:
> > Products F2 drop down box
> > Options D19 drop-down box based on selection in cell F2.
> >
> > Tables:
> >
> > Product 1: PECB
> > PECB Options List found in range AA5:AA10 (range called "OPTIONS-A")
> >
> > Product 2: GLUB
> > GLUB Options List found in range AB5:AB15 (range called "OPTIONS-B")
> >
> > How do I get the range result from a Lookup formula or VBA's AutoFilter to
> > show the values within the range?
>
>
>