Table 1: Column A contains the dates: A2=January 1st, A3=January 2nd, A4=January 3rd...A365=December 30th, A366=December 31st
Line 1 represents the year: B1=1990, C1=1991, D1=1992,...V1=2010, W1=2011
The middle is the data corresponding to the year and day.
Explanation Table 1 is the original data table.
Select A2:A366 - Insert - Name - Definition (named "Date") - OK.
Select B1: W1 - Insert - Name - Definition (named "Year") - OK.
Table 2: A1=Year—Select B1—Data—Validity—Settings (Allow: Sequence—Source:="Year")—OK
A2=Month--Select B2--Data--Validity--Settings (Allow: Serial--Source:="Date")--OK--Select B2--Right-click--Format Cell--Date- —Type (March 14)
Finally copy this formula to A3: =OFFSET(Sheet1!A1, MATCH(B2, date,), MATCH(B1, year,))
In the future, as long as you select the year and date, the corresponding cell data will be queried.
Note: Because some of the years are run-time years, you can add February 29th in February (just use text format).
If there is anything else you don’t understand. HI me directly