Data Validation & Dependent Lists

[Guest]

Using the “Data Validation - Create Dependent Lists†instructions on Debra
Dalgleish's Contextures website as a guide, I set up a parent list (Phase)
and dependent list (Activity). For a given Phase, there are multiple
Activities. For each Activity within a given Phase there is also a
Capitalization Flag (“Yes†or “Noâ€).

After a user has selected a Phase (in column A), and then an associated
Activity (in column B), I need a formula in column C that will automatically
display the Capitalization Flag associated with the Activity that was
selected in column B.

Please note that my parent list contains “illegal†characters (spaces), so I
followed Debra’s instructions for “Using Items with Illegal Charactersâ€
(http://www.contextures.com/xlDataVal02.html#Illegal).

I would greatly appreciate any help in creating a formula in column C that
will automatically display the Capitalization Flag associated with the
Activity that was selected in column B.

Thanks,
Bob

 

[Debra Dalgleish]

It depends on where the Capitalization Flag information is stored. If
it's in a column to the right of each Activity list, you could use a
Vlookup formula. For example:

=VLOOKUP(D8,OFFSET(INDIRECT(SUBSTITUTE(C8," ","")),0,0,,2),2,0)

where the selected activity is in D8 and the Phase is in C8

 

[Guest]

Debra,
Yes, I should have mentioned that the Capitalization Flag information is
stored in a column to the right of each Activity list. So your formula
worked perfectly!
Thanks for your help. I sincerely appreciate it.
Bob

P.S. If you don't mind me saying, your web site is phenomenal! It's the
first Excel-related website that I visit whenever I need help with a feature,
function, or formula. Thanks for being so generous with your expertise.

 

[Debra Dalgleish]

You're welcome, and thanks for letting me know that the formula worked.

Thanks also for your kind words about the web site, and I'm glad you
find the information useful. A few other people have contributed their
sample files and tutorials there too, which I really appreciate.