Link to home
Create AccountLog in
Avatar of flfmmqp
flfmmqp

asked on

Data Validation Excel 2010

I am trying to use data validation to keep things simple for my user.  We have a month cell (Jan, Feb, Mar) ect and a Date cell (1-31).  

I would like the date cell to look at the month cell and only have the valid number of days.  ie if Jan is picked then date would only have 1-31 but if feb is chosen then date would have 1-28.  

How can I do this?
ASKER CERTIFIED SOLUTION
Avatar of felixdsouza
felixdsouza
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of NBVC
Create 2 lists.  One that has all the Months (name this list MonthList) and then create a list of numbers from 1 to 31 and call this list DayList.

For the Month validation, use List and enter =MonthList.

Then for Day validation, use

=INDEX(DayList,1,1):INDEX(DayList,DAY(EOMONTH($H$2&1+0,0)),1)

where H2 is cell with Month drop down.
Avatar of flfmmqp
flfmmqp

ASKER

How do I do this excel 2010.  I don't see list as an option.
You need to go to Data -> Data Validation and select "List" in the Allow dropdown box...
Avatar of flfmmqp

ASKER

I figured it out this worked.  Thanks for the help.