Confusion with data validation (list)

[quemuenchatocha]

Hello cordial greetings

I am new, I hope I am not making a mistake at the time of posting.

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).

Example_Weather.xlsx
	A	B	C	D	E	F	G
1
2
3
4
5
6		Month	December		High Temp	50	50
7		Day	25		Low Temp	27
8		Year	2019		Precip	0
9					Snow	0
10
11
Hoja1

Cell Formulas
Range	Formula
G6	G6	=INDEX(High,MATCH($C$8&$C$6&$C$7,Year&Name&Day,0))
F6	F6	=IF(INDEX(High,MATCH($C$8&$C$6&$C$7,Year&Name&Day,0))<0,"Not available",INDEX(High,MATCH($C$8&$C$6&$C$7,Year&Name&Day,0)))
F7	F7	=IF(INDEX(Low,MATCH($C$8&$C$6&$C$7,Year&Name&Day,0))<0,"Not available",INDEX(Low,MATCH($C$8&$C$6&$C$7,Year&Name&Day,0)))
F8	F8	=IF(INDEX(Precip,MATCH($C$8&$C$6&$C$7,Year&Name&Day,0))<0,"Not available",INDEX(Precip,MATCH($C$8&$C$6&$C$7,Year&Name&Day,0)))
F9	F9	=IF(INDEX(Snow,MATCH($C$8&$C$6&$C$7,Year&Name&Day,0))<0,"Not available",INDEX(Snow,MATCH($C$8&$C$6&$C$7,Year&Name&Day,0)))

Named Ranges
Name	Refers To	Cells
Day	=Data!$D$5:$D$45388	F6:F9, G6
High	=Data!$E$5:$E$45388	F6:G6
Low	=Data!$F$5:$F$45388	F7
Name	=Data!$C$5:$C$45388	F6:F9, G6
Precip	=Data!$G$5:$G$45388	F8
Snow	=Data!$H$5:$H$45388	F9
Year	=Data!$A$5:$A$45388	F6:F9, G6

Cells with Data Validation
Cell	Allow	Criteria
C6	List	=Data!$M$5:$M$16
C7	List	=Data!$Q$5:$Q$35
C8	List	=Data!$K$5:$K$128

Example_Weather.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Historical Weather Lookup
2	Boulder, Colorado (USA)				Data is at:	Boulder, Colorado Climate and Weather Data: NOAA Physical Sciences Laboratory
3
4	Year	Month	Name	Day	High	Low	Precip	Snow			Year	Month	Name				Day
5	1897	1	January	1	-998	-998	-998	-998			1897	1	January	January	January		1
6	1897	1	January	2	-998	-998	-998	-998			1898	2	February	February	January		2
7	1897	1	January	3	-998	-998	-998	-998			1899	3	March	March	January		3
8	1897	1	January	4	-998	-998	-998	-998			1900	4	April	April	January		4
9	1897	1	January	5	-998	-998	-998	-998			1901	5	May	May	January		5
10	1897	1	January	6	-998	-998	-998	-998			1902	6	June	June	January		6
11	1897	1	January	7	-998	-998	-998	-998			1903	7	July	July	January		7
12	1897	1	January	8	-998	-998	-998	-998			1904	8	August	August	January		8
13	1897	1	January	9	-998	-998	-998	-998			1905	9	September	September	January		9
14	1897	1	January	10	-998	-998	-998	-998			1906	10	October	October	January		10
15	1897	1	January	11	-998	-998	-998	-998			1907	11	November	November	January		11
16	1897	1	January	12	-998	-998	-998	-998			1908	12	December	December	January		12
17	1897	1	January	13	-998	-998	-998	-998			1909						13
18	1897	1	January	14	-998	-998	-998	-998			1910						14
19	1897	1	January	15	-998	-998	-998	-998			1911						15
20	1897	1	January	16	-998	-998	-998	-998			1912						16
21	1897	1	January	17	-998	-998	-998	-998			1913						17
Data

Cell Formulas
Range	Formula
K5:K128,L5:L16	K5	=UNIQUE(A5:A45388,FALSE)
M5:M16,C5:C21	M5	=IF(L5=1,"January",IF(L5=2,"February",IF(L5=3,"March",IF(L5=4,"April",IF(L5=5,"May",IF(L5=6,"June",IF(L5=7,"July",IF(L5=8,"August",IF(L5=9,"September",IF(L5=10,"October",IF(L5=11,"November","December")))))))))))
N5:N16	N5	=IFS($L5,$M5,$L6,$M6,$L7,$M7,$L8,$M8,$L9,$M9,$L10,$M10,$L11,$M11,$L12,$M12,$L13,$M13,$L14,$M14,$L15,$M15,$L16,$M16)
O5:O16	O5	=IFS(1,$M$5,2,$M$6,3,$M$7,4,$M$8,5,$M$9,6,$M$10,7,$M$11,8,$M$12,9,$M$13,10,$M$14,11,$M$15,12,$M$16)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Month	=Data!$B$5:$B$45388	L5, C5
Year	=Data!$A$5:$A$45388	K5

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.

 

[jorismoerings]

Hi and welcome to MrExcel,

Kudos for providing enough information to help you, well done!
The solution is to work with a dynamic array which depends on the excel version you're using.
As you mentioned the version is Excel365 so the first option would be good for you.

See if this helps:

Book1((Unsaved-309027512575542192)).xlsb
	B	C	D	E	F	G	H	I	J
5
6		februari
7					1			1
8		2019			2			2
9					3			3
10					4			4
11					5			5
12					6			6
13					7			7
14					8			8
15					9			9
16					10			10
17					11			11
18					12			12
19					13			13
20					14			14
21					15			15
22					16			16
23					17			17
24					18			18
25					19			19
26					20			20
27					21			21
28					22			22
29					23			23
30					24			24
31					25			25
32					26			26
33					27			27
34					28			28
35
Sheet1

Cell Formulas
Range	Formula
F7:F34	F7	=SEQUENCE(DATE(C8,MONTH(C6&1)+1,1)-DATE(C8,MONTH(C6&1),1))
I7:I34	I7	=ROW(INDIRECT("1:"&DATE(C8,MONTH(C6&1)+1,1)-DATE(C8,MONTH(C6&1),1)))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
C7	List	=$F$7#

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Data validation (list), confusion.
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[quemuenchatocha]

Dear @jorismoerings thank you very much for your valuable information, you got me out of a problem that I could not find a solution. your two contributions work perfectly and is exactly what I needed, a big hug. I apologize, I don't know if I should leave you points or mark the answer as the solution, I'm new to this and so far I'm just learning how the forum works.
Again thank you very much!

 

[quemuenchatocha]

Dear @RoryA
Sorry, it won't happen again. I will follow the rules of the site for future questions.