Validation List - Multiple IFs - Formula to big

[ffialho]

I´m working on a formula for a validation list. What I´m doing is:

User chooses the month: May ( for example - the drop down has all 12 months )
Once the month is choosen - there is a second drop down list with the days of that specific month.

I´ve named range all the months

My problem:

The formula below is too big for the validation list formula: J2 is the field where the month is choosen. Is there a way a can reduce to use on the validation list formula or transform it into a macro?!

=IF($J$2=$Y$2;INDIRECT($Y$2);IF($J$2=$Z$2;INDIRECT($Z$2);IF($J$2=$AA$2;INDIRECT($AA$2);IF($J$2=$AB$2;INDIRECT($AB$2);IF($J$2=$AC$2;INDIRECT($J$2);IF($J$2=$AD$2;INDIRECT($J$2);IF($J$2=$AE$2;INDIRECT($J$2);IF($J$2=$AF$2;INDIRECT($J$2);IF($J$2=$AG$2;INDIRECT($J$2);IF($J$2=$AH$2;INDIRECT($J$2);IF($J$2=$AI$2;INDIRECT($J$2);IF($J$2=$AJ$2;INDIRECT($J$2);$IV$4))))))))))))

any help would be gratefull.

thanks

 

[Redwolfx]

See if you can use this....

With this setup you only need Named Ranges 1 for the Months and 1 for the Days.

And the Key to convert the Month Name to Number

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Named Range - Months
[/TD]
[TD][/TD]
[TD]Named Range Cells C2:C32 - Days
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Months
[/TD]
[TD]Month #
[/TD]
[TD]Days - Drag this formula Down to Row 32
[/TD]
[TD]Cell J2
[/TD]
[TD]Other Cell for Days Range
[/TD]
[/TR]
[TR]
[TD]January
[/TD]
[TD]1
[/TD]
[TD]=IF(MONTH(DATE(YEAR(TODAY()),VLOOKUP($J$2,$A$2:$B$13,2,FALSE),ROWS($A$1:$A1)))=VLOOKUP($J$2,$A$2:$B$13,2,FALSE),DATE(YEAR(TODAY()),VLOOKUP($J$2,$A$2:$B$13,2,FALSE),ROWS($A$1:$A1)),"")
[/TD]
[TD]Data Validation - List Months
[/TD]
[TD]Data Validation - List Days
[/TD]
[/TR]
[TR]
[TD]February
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]March
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]April
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]May
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]June
[/TD]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]July
[/TD]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]August
[/TD]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]September
[/TD]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]October
[/TD]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]November
[/TD]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]December
[/TD]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[ffialho]

Thanks Redwolfx!

This worked great!!! Exactly what I needed