Can you beat chat GPT on this question

[Beau the dog]

I may not be asking Chat GPT the right question, or in the right way. So I'm going to try here, as this forum has always come up with the right answer!

One Sheet1 in cells A1:L1 I have - Jan-2023, feb-2023, Mar-2023 and so on up to Dec-2023.
On Sheet2 I have a list of training course titles in cells V5:AC5 (8 titles). starting 3 rows below that (ignore the two rows just beneath the course titles) are dates in which these course are completed, each row represents one member of staff. There are also future dates added, in which courses are booked.

The challenge is....underneath the month dates in sheet1, to have a list of all the future training course titles that are booked underneath the correct month and year in Sheet1

For example, May-2023 would look like the example below, as there are two OLAT courses booked, and one Daily Check course booked in May 2023
Dec-2023 would look like the below, as one First aid course and one manual handling course are booked in the future

As always, any help would be greatly appreciated.

Sheet1

Jan-2023	Feb-2023	Mar-2023	Apr-2023	May-2023	Jun-2023	Jul-2023	Aug-2023	Sep-2023	Oct-2023	Nov-2023	Dec-2023
				OLAT							First Aid
				OLAT							Manual handling
				Daily checks

Sheet2

OLAT	Driver hours	First aid	Daily checks	Manual handling	Load Security	LoCITY	Bridge Strikes
5	5	3	5	5	5	5	5
20/03/2021		20/03/2023
02/05/2023
			11/05/2023
	21/04/2023	14/12/2024		15/12/2023
10/08/2023			20/11/2023
15/05/2023

 

[Beau the dog]

Can you write here the formula that you use in G6?

=LET(Tutto,'Training Matrix'!$V$5:$AC$32,Uno,IF((Tutto<>"")*(IFERROR(IF(YEAR(Tutto)>2020,TRUE,FALSE),FALSE)),COLUMN(Tutto)-COLUMN(INDEX(Tutto,1,1))+1&"#"&Tutto,""),Tuno,TEXTJOIN("|",TRUE,Uno),cCol,TEXTSPLIT(Tuno,"#","|"),inMese,FILTER(cCol,TEXT(INDEX(cCol,0,2),"aaaa-mm")=TEXT(F$5,"aaaa-mm")),IFERROR(INDEX(INDEX(Tutto,1,0),1,INDEX(inMese,0,1)),"--"))

 

[Anthony47]

Why are you using "aaaa-mm" and not "yyyy-mm"?

 

[Anthony47]

And why in G6 are you referring to the date in F5?

 

[Beau the dog]

Why are you using "aaaa-mm" and not "yyyy-mm"?

That's how the formula came through when you first sent it? I just changed it and it just gave me blanks along the row.

I usually use date format dd/mm/yy

But for this I just wanted the month and year, as this was for the Spreadsheet dashboard

 

[Beau the dog]

And why in G6 are you referring to the date in F5?

I moved it back one again, the months are now listed in F5:Q5

Apologies, I amended the formula to suit, as far as I understood, so just the G5 to F5

 

[Anthony47]

You may display the date in your preferred format, regardless how the formulas use them
I guess the formula I suggested use "yyyy-mm", even thoug the XL2BB shows "aaaa-mm"

But let's try making it work

 

[Beau the dog]

You may display the date in your preferred format, regardless how the formulas use them
I guess the formula I suggested use "yyyy-mm", even thoug the XL2BB shows "aaaa-mm"

But let's try making it work

I just realized there are two aaaa-mm to change...which I have done and now it works!

Thank you so much, really appreciate your time and patience. You sir, are a legend!

 

[Anthony47]

Let me amend the formula:

=LET(Tutto,Sheet2!$V$5:$AC$100,LForMonth,B1,Uno,IF((Tutto<>"")*(IFERROR(IF(YEAR(Tutto)>2020,TRUE,FALSE),FALSE)),COLUMN(Tutto)-COLUMN(INDEX(Tutto,1,1))+1&"#"&Tutto,""),Tuno,TEXTJOIN("|",TRUE,Uno),cCol,TEXTSPLIT(Tuno,"#","|"),inMese,FILTER(cCol,TEXT(INDEX(cCol,0,2),"yyyy-mm")=TEXT(LForMonth,"yyyy-mm")),IFERROR(INDEX(INDEX(Tutto,1,0),1,INDEX(inMese,0,1)),"--"))

This way you have only to adapt:

Tutto, Sheet2!$V$5:$AC$100    'the area with the courses
LForMonth,   B1               'the date to look for

 

[Beau the dog]

Let me amend the formula:

=LET(Tutto,Sheet2!$V$5:$AC$100,LForMonth,B1,Uno,IF((Tutto<>"")*(IFERROR(IF(YEAR(Tutto)>2020,TRUE,FALSE),FALSE)),COLUMN(Tutto)-COLUMN(INDEX(Tutto,1,1))+1&"#"&Tutto,""),Tuno,TEXTJOIN("|",TRUE,Uno),cCol,TEXTSPLIT(Tuno,"#","|"),inMese,FILTER(cCol,TEXT(INDEX(cCol,0,2),"yyyy-mm")=TEXT(LForMonth,"yyyy-mm")),IFERROR(INDEX(INDEX(Tutto,1,0),1,INDEX(inMese,0,1)),"--"))

This way you have only to adapt:

Tutto, Sheet2!$V$5:$AC$100    'the area with the courses
LForMonth,   B1               'the date to look for

I just realized there are two aaaa-mm to change...which I have done and now it works! So the original formula was perfectly fine, it was just me trying to muddle my way through it....poorly!

Thank you so much, really appreciate your time and patience. You sir, are a legend!

 

[Anthony47]

Thank you for the feedback