Solved: Data Input Restrictions

[chem101]

Hello Everyone,

Is it possible to restrict data selected in one field based on items selected in another? I have two cells on a spreadsheet. Both have a drop down list of items set up through Data Validation. The drop down list for the first cell contains these items: A, B, C, AA, BB, and CC. The second cell has a drop down list with several items (too many to list here). The items in this second drop down list end in either SY or SW (ex:150SY or 250SW or ERT350SY). Item in list one A, B, C can only be paired with items from list two that end in SY. Items from list one AA, BB, and CC can only be paired from the items in list two that end in SW. Can these input restrictions be put in place using VBA code? If so, can you help me with this?

Thank you for any assistance you can provide!

[Bob Phillips]

Have separate lists for the SY and SW types, and then a simple IF in the second to determine which to show.

[chem101]

I'm not sure what you mean. Would you explain a little further please?
Thank you!

[Bob Phillips]

Create one list in M1:Mn say of values 150SY, ERT350SY, ...

Add a name of SY with a formula of =OFFSET($M$1,,,COUNTA($M:$M),1)

Create a second list in N1:Nn say of values 250SW, ...

Add a name of SW with a formula of =OFFSET($N$1,,,COUNTA($N:$N),1)

Assuming the first DV is in C1, in the second DV, with a type of List add a formula of =IF(OR($C$1="A",$C$1="B",$C$1="C"),SY,SW)

[chem101]

Thank you for your help!! I was able to solve this issue without using the Offset formulas. I used the If/Or statement in Conditional Formatting and just referenced the named ranges SW and SY. T
Thank you again for your assistance!

[Bob Phillips]

In CF? Are you sure?

The Offset was just so that you could additems to the lists without changing anything.

[chem101]

Yes, in CF. Fortunately both lists are constant and shouldn't change.
Thank you again!!

[Bob Phillips]

I thought we were talking DV not CF.