VBA Data Validation depending on Target Value

Skybluekid

Well-known Member
Joined
Apr 17, 2012
Messages
1,224
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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)?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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/
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top