Need a conditional drop-down list (list contents)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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)
 
Back
Top