If Statement with data validation based on a letter

[dagindi]

I am trying to put an if statement into data validation based on the following:

C5 has a name range list, "List1" consisting of:

DILN
DILC
DILG
DIMN
DIMC
DIMG
DIHN
DIHC
DIHG
NJLN
NJLC
NJLG
NJMN
NJMC
NJMG
NJHN
NJHC
NJHG

The 3rd letter in each code is either L, M, or H.

I want the data validation list in E5 to be based on if the 3rd letter is:

L then the data validation should equal CoList1
M then the data validation should equal CoList2
H then the data validation should equal CoList3

"CoList1", "CoList2" & "CoList3" are additional named ranges in the sheet

[Richard Buttrey]

Hi,

See attached.

Rgds

[JBeaucaire]

I placed the text strings L M and H in P5:P7 and named the cells Codes. Then this DV formula works in E5:

=CHOOSE(MATCH(MID(C5,3,1),Codes,0), CoList1, CoList2, CoList3)


..if you're interested in a non-VBA approach.

[dagindi]

Both solutions work well.

I didn't even consider a vba approach so I want to look at that on my own a little more closely.

JB's solution addresses something that RB's doesn't (and that I didn't think about to be honest) which is if C5 is blank then E5 is "inactive". However if you select a choice from C5, then from E5, and then make C5 blank, E5, while inactive, still holds the value.

This pointed out another flaw in my thinking which is if C5 is blank, at any point, then E5 should be blank and "inactive", if possible.

[JBeaucaire]

To stay with non-VBA solution, I often use a conditional formatting trick on the second drop down cell that compares the cell's current value to the drop down that SHOULD exist, and if the value in the cell IS in the list in that drop down, stay visible, but if the drop down doesn't match (or doesn't exist, such as when the first cell is blank), then color the second cell white font so any straggler entries are invisible to the user.

In the attached sheet, change the first cell to something that doesn't have 5% in the list and the 2nd cell will hide the now-incorrect value.

[dagindi]

JB,

Interesting way of handling this type of problem.

For the time being it works. I know once I apply this to the "big picture" i am going to have to tweak this.

When I get there, if i cant figure it out I'll post a new thread

Thanks for the all the help.