How to restrict user to make wrong selection in Drop down list?

[mso3]

Hi friends,

I have created a dependent drop down list for State, District and block.

If a user selected a drop down list then it is possible to change the name of State or District by other user resulting a wrong State, District and Block selection. I don't want to allow any user to change such wrong indirect selection.

For example:
If user select State 'B'; then the district should be either 'R, S, T'.
Here it is possible to change the State 'B' to either 'A, C, D, E' which is wrong after the selection by first user.
It's also possible to change the District after selection by first user.

I want to restrict the user to change dependent list. If the user wants to change the drop down list then he must have to change the dependent list.
In short ...
If he changed the State then he must change District and Block.
If he changed the District then he must change Block.

Any help will be highly appreciated.

Thanking you,

[tim201110]

maybe check for correspondence between j6,k6 & l6 before further calculations&?

[mso3]

Yes,

Thank you.

[JohnTopley]

Insert the code below in tab "Personal":

Right-click tab, select "view code" and paste code.

If State is changed, then District is set to blank and if District is changed Block is set to blank

Please Login or Register  to view this content.

See http://www.contextures.com/xlDataVal02.html#clear

Change 10 and 11 in code for your actual columns

[mso3]

Hi John,

Thank you. It's very close to my requirement. I have made a little amendment in it to clear 2 columns to the right if the user edit State.

Requirement:

If the user edit State clear 2 columns to right. That is District and Block.
If the user edit District then clear 1 column to left and 1 column to right. That is State and Block.
If the user edit block then clear 2 columns to left. That is State and District.

In short I want to select the drop down list from column 'J' to 'L' direction only. User can not edit the drop down list from District or Block.

Please Login or Register  to view this content.

Please tell me what is the meaning of

Please Login or Register  to view this content.

Thank you.

[JohnTopley]

You cannot clear State/District if you enter Block because entry of Block is treated as a Change to it will clear State/District.

As block SHOULD be dependent on State/District it makes no sense to clear these.

Please Login or Register  to view this content.

If Target.Validation.Type = 3 =DATA VALIDATION

[mso3]

Hi John,

Thank you.

Have a nice time.