help with secondary list validation

[milred]

I am trying to validate against a secondary set of criteria... here is my
situation:

My validation data is in a table BA2:CD43... main categories across the top
row, and sub-categories entered in columns directly beneath each associated
main category (some categories have up to 30 sub-categories). Elsewhere, I
want to enter cats & sub-cats in two columns, validated against the sub-cat,
based on what is chosen for the main cat. Validating the main cat is no
problem, but I can't figure out how to validate against the columns of
sub-cats... I can't write a formula with 30 or more nested IF statements.
Any suggestions???

I am running Office 2003. Please let me know if you need any further info.
Thanks!

 

[Andy B]

Hi

Have a look here:
http://www.contextures.com/xlDataVal02.html
If I understand you correctly, you should find some guidelines here.

 

[Max]

Here's one pitch ..

Assuming your table is in Sheet1, in BA2:CD43
with the top row of Main Cats in *row2*, viz. something like:

MCat1............MCat2........>> etc
MC1_SCat1 MC2_SCat1
MC1_SCat2 MC2_SCat2
MC1_SCat3 MC2_SCat3
MC1_SCat4 MC2_SCat4
MC1_SCat5 MC2_SCat5
etc

In say, Sheet2
-----------------

Assuming the Main Cats and Sub Cats would be entered in cols A and B
from row2 down:

Put in C2:

=IF(ISNA(MATCH(TRIM(A2),Sheet1!$2:$2,0)),"No such Main Cat","OK")

Put in D2:

=IF(C2="No such Main
Cat","-",IF(ISNA(MATCH(TRIM(B2),OFFSET(Sheet1!$A$1,,MATCH(TRIM(A2),Sheet1!$2
:$2,0)-1,100),0)),"No such SubCat","OK"))

Select C22 and copy down

Cols C and D will return the results of the validation checks for the Main
and Sub Cats

The Sub Cat check in col D will return "-" if the Main Cat check in col C is
void
(i.e. if "No such Main Cat" is returned in col C), otherwise it will check
the Sub Cat entered in col B against the Sub Cat list in the appropriate
Main Cat col in the reference table in Sheet1

(Sub Cat validation check is dependent on the Main Cat being valid in the
first place)

The "100" height parameter inside OFFSET() in col D is an arbitrary number
(which covers up to row100), but should more than suffice
since your table in BA2:CD43 comprises only 42 rows "tall".
Adjust this number higher if necessary.

TRIM() is used to increase robustness of matching, in case there are any
inadvertent
leading, trailing or extra in-between spaces in the Main Cat and Sub Cat
descriptions
entered in cols A and B

 

[milred]

Andy,
Yes, that worked! Thanks so much for your help
--Jeff

 

[Andy B]

Thanks for the thanks!

--
Andy.

Andy,
Yes, that worked! Thanks so much for your help
--Jeff

 

[Max]

my take on the post was wildly-off ..
... I was pitching in the wrong ball-game ! <bg>

 

[milred]

Max,
Thank you too for your time and effort too. I greatly appreciate it.

 

[Max]

Ay, no problem, it's my pleasure milred !
Thanks for the feedback

There's always something learnt from every post
and it cumulates experience ..