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

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
272
Office Version
  1. 365
  2. 2010
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?)
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,488
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Nov 2, 2005
Messages
272
Office Version
  1. 365
  2. 2010
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...

Date_or_Text_as_Dependent_Data_Validation.jpg
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Nov 2, 2005
Messages
272
Office Version
  1. 365
  2. 2010
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,488
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,898
Messages
5,627,526
Members
416,250
Latest member
darius_rebelo

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
Top