VBA Data Validation depending on Target Value

Skybluekid

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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

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
829
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
829
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,893
Messages
5,489,561
Members
407,700
Latest member
SimpleJuan

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top