Change data validation type depending on the contents of a cell?

[kcmuppet]

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 e.g. force a date depending on A1?

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

 

[jasonb75]

Perhaps create another list using the current date as a reference point then use the indirect method to validate against that list of dates?

 

[kcmuppet]

Perhaps create another list using the current date as a reference point then use the indirect method to validate against that list of dates?

Thanks that's a good idea.

Just trying to think it through...

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.

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

 

[Fluff]

Cross posted Data validation *type* depending on contents of another cell

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

 

[kcmuppet]

Cross posted Data validation *type* depending on contents of another cell

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

Thanks for your message. This is also posted on Chandoo's forum here: Data validation *type* depending on contents of another cell

 

[jasonb75]

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

With a defined table as you are using, it should mirror the existing formatting in the same column.

I can format the Dependent Choice Column using a date format and it seems to tolerate either dates or text.

That applies to any format, the formatting on a cell is only applied to numbers (dates are numbers with special formatting). All number formats have an additional parameter for text which is effectively applied by default when the content of the cell can't be formatted as a number for any reason.