VBA Data Validation depending on Target Value

Skybluekid

Well-known Member
Joined
Apr 17, 2012
Messages
828
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.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,661
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

Well-known Member
Joined
Apr 17, 2012
Messages
828
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

Well-known Member
Joined
Nov 1, 2008
Messages
3,661
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

Well-known Member
Joined
Apr 17, 2012
Messages
828
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

Well-known Member
Joined
Nov 1, 2008
Messages
3,661
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/
 

Watch MrExcel Video

Forum statistics

Threads
1,102,302
Messages
5,486,052
Members
407,529
Latest member
netojose

This Week's Hot Topics

Top