VBA Data Validation depending on Target Value

[Skybluekid]

Hi All.

I have Data Sheet which logs Penalty Charge Notices. There is a column which has all Authorities, including Police. In another column i have a list of Fine Types, for both police and councils. What i would like to happen is if i choose a council then no police fine would show in a drop list and visa versa.

I have tried various vba code but it always generates an error and the corrupts the spreadsheet the next time it is opened.

Any ideas would be useful.

 

[sijpie]

A bit more information?
What are the (relevant) columns of the database?
How do I recognise that you have chosen a council? (or police)
How do i differentiate between council fines and police fines (in order to filter)?

 

[Skybluekid]

Thank you for the reply. On the main data sheet, in column B, I have a drop list of all authorities, inc police forces. In column F, i have a drop list of all the fines for both Council and police. This comes from a table on the Extra worsheet. In the table there is a "P" against all the fines that relate to police.

Hope this helps

 

[sijpie]

A few more questions:

You talk about a datasheet (called 'Data Sheet'?). This is where you log the administered fines.

The columns with authorities and fines, are they on the same sheet (bad practice) or on different sheets?

Your dropdown lists, are they on the sheet (which?) or in a pop-up userform? What are the names of these dropdown lists? Are they ActiveX lists or Form lists?

 

[Skybluekid]

Yes, the data sheet is where the fines are logged. The fines and authrorites are on a sheet called Extra. The Dropdown lists are Data Validation lists on the Data Sheet.

Hope this helps

 

[sijpie]

In the sheet Extra, is it not possible to have two additional ranges "Council" and "Police" where each holds a filtered list of the fines.
Then in the Data sheet, you add a (hidden) column that checks if the authority selected is Police or Council.

Then you can set the formula for the validatiion of the fines to point to the correct list using =INDIRECT().
See for instance https://trumpexcel.com/excel-drop-down-list/