• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data validation *type* depending on contents of another cell

k1s

Member
Hi,

Does anybody know how to change data validation type depending on the contents of a cell?

Whereas I can use lists and indirect for a list in B1 depending on the contents of A1, how could I make the data validation for B1 force a date, if depending on A1?

(Moderator - I'm guessing it'll need some VBA, so should this post sit in that forum?)
 

Peter Bartholomew

Well-Known Member
Before embarking on VBA, you might like to consider a formula
Code:
Let validate? refer to:

= IF(date?,
  COUNTIFS(input, ">="&startDate, input, "<"&endDate),
  COUNTIFS(list, input))
 

k1s

Member
@Peter Bartholomew ,

Thanks for your reply. To clarify, I already have data validation in Column B that permits selection from one of several lists depending on what is selected in A (i.e. list-based data validation, e.g. user can select in A1 Fruit or Veg: If Fruit is selected, then the list in B1 shows Apples, Oranges.

What I would like to do is switch between that and a date-based validation, e.g. if the user selects Veg, they're actually forced to enter a valid date.
 

Peter Bartholomew

Well-Known Member
If you were to adopt a formula-based solution you would have to say goodbye to the pretty little drop down! What you would need to do is take whatever formula you use to define the data validation list B1 and instead use Name Manager to allow the name 'list' to refer to the formula. The validation formula then becomes
= validate?
Otherwise you need to follow up with idea of using VBA. This would require an event handler and knowledge of the part of the Excel object model that applies Validation rules to a Range object.
 
Last edited:

k1s

Member
Here's what I've gone with went with in the end, based on a suggestion over at MrExcel:

1. Create a dynamic list of dates (unfortunately my users wont have versions of Excel with dynamic array formulae, so it will be a Table listing valid dates)

2. Define a name for the column of valid dates that can go in the dependent list

3. Use that defined name as one of the options in the first list

I think I've managed to make that work. I was concerned about how to format the dependent cells so that they show a selected date as a date and not as a date value, while also being able to show a valid text as text. What surprises me is that I can format the Dependent Choice Column using a date format and it seems to tolerate either dates or text.


72265

I wonder how robust that will be if new rows are added to the tables...


(Moderator - looks like it's not VBA solution after all!)
 
Top