dependent validation lists; changes to one does not prompt error mssg for other

[lroane]

Hello - The following cells are dependent drop-down lists (i.e. the available PN is dependent on the Drwg selected and the available Drawing is dependent on the Sterilization Method selected).

Sterilization Method : 0
Drawing : 0
Part Number : 0

The problem I have is that after I select valid entries (see below), but then I have go back up to change the Drawing value to 58. Nothing prompts me to also change the Part Number which is not a valid choice for Drawing 58.

Sterilization Method : Dry
Drawing : 24
Part Number : 254566

I'm not familiar with VBA, but I'lll try any suggests. Thank you!

[Carim]

Hi,

Your problem is not a VBA problem ...
Solution is very well explained by Debra

http://www.contextures.com/xlDataVal02.html

HTH
Carim

[lroane]

I used Contextures to get this far .

I didn't explain the problem well enough. I enter my data, the data validations work. If I enter the data and then go back up and change a value (Drawing from 24 to 28), then the Part Number validated cell does not prompt me to change it's value (254556 is not a valid Part Number for Drawing 28).

Corresponding
Drawing Part Number
24 254556
24 325469
24 258768
28 868975
28 256877

[Maistrye]

Hello - The following cells are dependent drop-down lists (i.e. the available PN is dependent on the Drwg selected and the available Drawing is dependent on the Sterilization Method selected).

Sterilization Method : 0
Drawing : 0
Part Number : 0

The problem I have is that after I select valid entries (see below), but then I have go back up to change the Drawing value to 58. Nothing prompts me to also change the Part Number which is not a valid choice for Drawing 58.

Sterilization Method : Dry
Drawing : 24
Part Number : 254566

I'm not familiar with VBA, but I'lll try any suggests. Thank you!

I've run into this several times. Generally, my solution to the problem depends on the sheet that I am working on. Some of my worksheets have no macros at all, and I hesitate to add them (since the people that use the sheets don't enable them).

In these situations, I usually just add conditional formatting. For example, for the above, I'd add a conditional format to check and see if the value in the cell is valid relative to the master cell. If it isn't, I change the background to be red. (My favorite colour for errors)

Something like the following for the conditional format formula (if B1 is the cell with the dependent value, and A1 is the cell with the master value):
=ISERROR(MATCH(B1,INDIRECT(A1),0))

Otherwise, you'll have to go to VBA. This will take a bit more work. You'll probably want to use the OnChange event, check to see if the cell involved is one of the "Master" cells. If it is, you'll want to check the cell that is its' dependent. If the value is not valid, clear the cell.

I've not coded this particular portion in VBA before. I usually just go with the colouring because of the people who use the sheets.

Scott