VB, Index and Match- listing event details on Calendar

[Nicki]

Please could you look at my VB and formula on right side of "Mandatory Calendar" tab Calendar. I want to click on date and details appear on table on right side. Thanks guys

 

[Hui]

A few things

Firstly, Change the Course Dates in the Course Table Table, to Integers
They are currently 01-02-2018 9:00AM
Change them to just the dates eg: 1/2/2018 etc
the display format doesn't matter

Now remove the Conditional Formats from the Calendar

Then on the Mandatory Calendar worksheet

AS5: =IFERROR(INDEX(INDEX(Datum,0,3),SMALL(IFERROR(IF(INDEX(Datum,0,2)=(1*$G$2),ROW($AS$1:$AS16),9999),9999),ROW(D1))),"") Ctrl+Shift+Enter

AT5: =IFERROR(INDEX(INDEX(Datum,0,4),SMALL(IFERROR(IF(INDEX(Datum,0,2)=(1*$G$2),ROW($AS$1:$AS16),9999),9999),ROW(D1))),"") Ctrl+Shift+Enter

AU5: =IFERROR(INDEX(INDEX(Datum,0,5),SMALL(IFERROR(IF(INDEX(Datum,0,2)=(1*$G$2),ROW($AS$1:$AS16),9999),9999),ROW(D1))),"") Ctrl+Shift+Enter

AV5: =IFERROR(INDEX(INDEX(Datum,0,6),SMALL(IFERROR(IF(INDEX(Datum,0,2)=(1*$G$2),ROW($AS$1:$AS16),9999),9999),ROW(D1))),"") Ctrl+Shift+Enter

I also changed the VBA slightly

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Range("Calen")) Is Nothing Then
  Worksheets("Mandatory Calendar").Range("G2") = Int(Target.Value)
  Worksheets("Mandatory Calendar").Range("G2").NumberFormat = "d mmm yy"
End If
Application.Calculate

End Sub

or see the attached file:
To understand how those formula work please read:
https://chandoo.org/wp/formula-forensics-003/

If you want to add the Conditional Formatting back use a formula like :
=MATCH(B5,Table!$C$3:$C$24,0)

 

[Nicki]

A few things

Firstly, Change the Course Dates in the Course Table Table, to Integers
They are currently 01-02-2018 9:00AM
Change them to just the dates eg: 1/2/2018 etc
the display format doesn't matter

Now remove the Conditional Formats from the Calendar

Then on the Mandatory Calendar worksheet

AS5: =IFERROR(INDEX(INDEX(Datum,0,3),SMALL(IFERROR(IF(INDEX(Datum,0,2)=(1*$G$2),ROW($AS$1:$AS16),9999),9999),ROW(D1))),"") Ctrl+Shift+Enter

AT5: =IFERROR(INDEX(INDEX(Datum,0,4),SMALL(IFERROR(IF(INDEX(Datum,0,2)=(1*$G$2),ROW($AS$1:$AS16),9999),9999),ROW(D1))),"") Ctrl+Shift+Enter

AU5: =IFERROR(INDEX(INDEX(Datum,0,5),SMALL(IFERROR(IF(INDEX(Datum,0,2)=(1*$G$2),ROW($AS$1:$AS16),9999),9999),ROW(D1))),"") Ctrl+Shift+Enter

AV5: =IFERROR(INDEX(INDEX(Datum,0,6),SMALL(IFERROR(IF(INDEX(Datum,0,2)=(1*$G$2),ROW($AS$1:$AS16),9999),9999),ROW(D1))),"") Ctrl+Shift+Enter

I also changed the VBA slightly

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Range("Calen")) Is Nothing Then
  Worksheets("Mandatory Calendar").Range("G2") = Int(Target.Value)
  Worksheets("Mandatory Calendar").Range("G2").NumberFormat = "d mmm yy"
End If
Application.Calculate

End Sub

or see the attached file:
To understand how those formula work please read:
https://chandoo.org/wp/formula-forensics-003/

If you want to add the Conditional Formatting back use a formula like :
=MATCH(B5,Table!$C$3:$C$24,0)

Great job I can see why you guys are called Ninja . Thanks indeed for your help and explanation which helped me a lot to learn my mistakes.

Sorry I am bothering you again, If i edit or remove any date the green colour of that edited date will stay as green on Calendar. how can i get rid of them.

I appreciate your time and help. Thanks, Nicki

 

[Hui]

Select the calendar and set the Fill color as None

Also Change the VBA as shown below

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Target, Range("Calen")) Is Nothing Then
  Worksheets("Mandatory Calendar").Range("G2") = Int(Target.Value)
  Worksheets("Mandatory Calendar").Range("G2").NumberFormat = "d mmm yy"
End If
Application.Calculate

End Sub

The code change won't effect your issue but stops errors popping up

 

[Nicki]

Thanks Hui, Fantastic.

 

[Nicki]

Could you please check why some of the date are not showing up randomly , Mandatory Calendar, have I confused it with my conditional formatting?! Please help.

 

[Nicki]

Could you please check why some of the dates added on "Table" sheet below row 20 wont show up on Mandatory Calendar, Please help.
this is my Datum range: =OFFSET(Table!$B$3,,,COUNTA(Table!$C$3:$C$1537)-1,6)

 

[Nicki]

Hi Hui, ignore my previous message, I have corrected it myself by changing the Rows number AS18 TO 30
=IFERROR(INDEX(INDEX(Datum,0,3),SMALL(IFERROR(IF(INDEX(Datum,0,2)=(1*$G$2),ROW($AS$1:$AS30),9999),9999),ROW(D1))),"")

Thanks for your help and time, You are brilliant at what you are doing.