Data validation (list), confusion.

[ferfer20]

Hello cordial greetings

I am a bit confused, I need to perform a data validation in cell C7, so that the days correspond to the month set in cell C6 and the year found in cell C8. The data is recorded in the DATA tab; I have tried INDEX + MATCH functions, as well as OFFSET, but none of them meet what I require (I think I am making a lot of mistakes). Can you please help me with a solution or give me a hint on how to find a formula that does what is required.

Exactly, what I am trying to do is that when selecting for example the month of January (C6) of the year 2018 (C8), in the drop-down list of the cell C7 only the 31 days corresponding to the specified month appear. That is to say, if I select February, 28 days will appear and not 30 or 31 days, with the exception of if it is a leap year, in which case, 29 days should appear for the month of February.

To do this, I want to select the data corresponding to Year (A5:A45388), Name (C5:C45388) and Day (D5:D45388), which are located in the "Data" tab, and depending on the year selected in the Sheet 1 tab (C8) and month (C6), bring me the respective days for that date.

Since January, March, May, July, August, October and December have 31 days, if I select any of these months, regardless of the year, those 31 days should appear in the drop-down list located in cell C7, and not 30 or 28 days.

The same would apply if you select the months of April, June, September and November, in which case the 30 days should appear in the drop-down list in cell C7 and not 31 or 28 (29 if February is part of a leap year).

Weather_1.png

Weather_2.png

Thank you very much for your valuable attention.

PS: I have assigned range names to the table found in the DATA tab from the values in the top row (column header) and I am using Excel 365.