I am trying to make a third category based on the 1st & 2nd categories (both of which use illegal characters) and I am having some trouble figuring out the correct data validation source to use.
I tried to copy the same as in the below post using a numberlookup table that has all that has all the possible options derived from the 2nd category. But it does not give me any options under the 3rd category when I do this. I also tried to use the following data validation source:
A2: =level
B2: =INDIRECT(VLOOKUP(A2,namelookup,2,0))
C2: =INDIRECT(SUBSTITUTE((VLOOKUP(A2,namelookup,
2,0))&(VLOOKUP(B2,numberlookup,2,0))," ",""))
The numberlookup being only one set of data corresponding to only one dropdown choice under the 2nd category. It appears to work for this one choice from the 2nd category but I am not sure how I would edit this data validation source to include all sets of data for each option in the 2nd category. Does anyone know how I would do this or if I am going about this wrong? Thanks!
Quote:
Originally Posted by
On Jan 23, 12:17 am, Debra Dalgleish wrote:
How are the item lists in your workbook set up?
wrote:
On Jan 12, 11:37 pm, Debra Dalgleish
wrote:
Debra,
Can you please explain to me how I can make the Illegal character
formula work for a 3rd category? I want to have the 3rd category
options dependent upon the combination of what is selected for both
categories 1 & 2. Please keep in mind that both categories 1 & 2
contain multiple words with illegal characters.
--
Debra Dalgleish
Contextureshttp://www.contextures.com/tiptech.html
Hi Debra,
I set everything up as described in the Illegal character section of
this article: http://www.contextures.com/xlDataVal02.html#Illegal.
I created name lookup tables "SymptomNameLookup" and
"GroupNameLookup". The data is extensive, so I had to use multiple
sheets within the same workbook, but it is working, except I cannot
get the list in category 3 to show the options available based on the
combination of selections from the 1st & 2nd category lists.
I'm pretty sure it is a syntax error in the data validation source for
category 3 (cell C2).
This is what I entered for the data validation source:
A2: =Symptom
B2: =INDIRECT(VLOOKUP(A2,SymptomNameLookup,2,0))
C2: =INDIRECT(VLOOKUP(A2,SymptomNameLookup,
2,0))&(VLOOKUP(B2,GroupNameLookup,2,0))
I simplified my workbook and so I could attach a sample file that may
help you understand what I've done, but I don't know how to attach
it. Do you?
Thanks for your help!
Tom |