Needing a UDF for a commission tracker...

[brockk]

Greetings, I don't think that this is possible by means of formula only. I am needing help to retain the last information/data according to attached picture:

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		SALES GOAL
2		2023	2024	2025	2026	2027		CELL B3 =	=IF(Jan!$F$7=B$2,Jan!$H$2,"-")
3	JAN	65,000	-	-	-	-		CELL C3 =	=IF(Jan!$F$7=C$2,Jan!$H$2,"-")
4	FEB	-	-	-	-	-		CELL D3 =	=IF(Jan!$F$7=D$2,Jan!$H$2,"-")
5	MAR	-	-	-	-	-		CELL E3 =	=IF(Jan!$F$7=E$2,Jan!$H$2,"-")				each category has the same formula. The only change that occurred is where the value of each category is located . These are:
6	APR	-	-	-	-	-		CELL F3 =	=IF(Jan!$F$7=F$2,Jan!$H$2,"-")
7	MAY	-	-	-	-	-		CELL B4 =	=IF(Feb!$F$7=B$2,Feb!$H$2,"-")
8	JUN	-	-	-	-	-		CELL C4 =	=IF(Feb!$F$7=C$2,Feb!$H$2,"-")
9	JUL	-	-	-	-	-		CELL D4 =	=IF(Feb!$F$7=D$2,Feb!$H$2,"-")				SALES GOAL VALUE IS = TO CELL $H$2 OF CORRESPONDING MONTH
10	AUG	-	-	-	-	-		CELL E4 =	=IF(Feb!$F$7=E$2,Feb!$H$2,"-")				ACTUAL SALES VALUE IS = TO CELL $H$4 OF CORRESPONDING MONTH
11	SEP	-	-	-	-	-		CELL F4 =	=IF(Feb!$F$7=F$2,Feb!$H$2,"-")				COMM. EARNED VALUE IS = TO CELL $H$6 OF CORRESPONDING MONTH
12	OCT	-	-	-	-	-		etc.
13	NOV	-	-	-	-	-
14	DEC	-	-	-	-	-		I have a template for each month of the year, where I have a drop-down list for the YEARS in cell F7 of each of the worksheets. What I will need is a macro/UDF that can retain the last value for the month of the particular year selected in this worksheet. Example: Jan - 2023 sales goal is 65k (given data from Jan!H2 cell) Jan - 2024 sales goal is 72k (updated from the same cell of the template only changing the year and amount of goal). I would like to retain the 2023 values for all 3 categories (Sales Goal, Actual Sales, Comm. Earned) and not have it get erased from the array so I may later analyze the data. More than likely I don't think that this can be done by formula alone thus, why I am asking for help from someone that knows macro. Any help is truly appreciated.
15
16		ACTUAL SALES
17		2023	2024	2025	2026	2027
18	JAN	127,128	-	-	-	-
19	FEB	-	-	-	-	-
20	MAR	-	-	-	-	-
21	APR	-	-	-	-	-
22	MAY	-	-	-	-	-
23	JUN	-	-	-	-	-
24	JUL	-	-	-	-	-
25	AUG	-	-	-	-	-
26	SEP	-	-	-	-	-
27	OCT	-	-	-	-	-
28	NOV	-	-	-	-	-
29	DEC	-	-	-	-	-
30
31		COMMISSIONS EARNED
32		2023	2024	2025	2026	2027
33	JAN	25,500	-	-	-	-
34	FEB	-	-	-	-	-
35	MAR	-	-	-	-	-
36	APR	-	-	-	-	-
37	MAY	-	-	-	-	-
38	JUN	-	-	-	-	-
39	JUL	-	-	-	-	-
40	AUG	-	-	-	-	-
41	SEP	-	-	-	-	-
42	OCT	-	-	-	-	-
43	NOV	-	-	-	-	-
44	DEC	-	-	-	-	-
45
Sheet1

Any help is truly appreciated!

 

[brockk]

Is this even possible?

 

[brockk]

Any suggestions at this point? Should I consider another route to go, as for the creation of the template. At this point I am completely stumped as I would like it to be all automated and not manually entering the EOM tallies. Thanks in advance for any help/suggestions that may be provided.