dynamic excel calendar help pls

[bbsin]

hi

I saw a youtube to create a dynamic calendar

the dates are using sequence, may I know how to have column D for text input 

Mon to Sun same for the rest of the columns

I want to have one column as the date and the other for input text
the calendar to dates to move accordingly,  I cannot input a column the date doesnt move..

Please advise. 

col C8 -=SEQUENCE(6,7,0)+DATE($C$4,$K$2,1)-WEEKDAY(DATE($C$4,$K$2,1),2)+1

to be like that

Thank you

[#2]

@bbsin 

I modified the formula so that you can input a text beside each date.

 

[#3]

hi Rachel

Thank you is there any way to auto populate the text from any other tab that match the date in the calendar?

to display eg Title, Category , Start to end date / time.

Attached sample tab , event data start and end time are from SP which contain Date and Time, but how to use Text Join to match it to the cal date and display across if it run few day?  Thank you

[#4]

@bbsin 

Yes, you can use Filter to put event next to date which is greater than start date and less than end date:

[#5]

@rachel 

hi Rachel,

Thank you but what is HSTrack?


I notice that u Run the event A to F, if I want to pick from selected columns, not all the columns Text, how to as the real data is very big.   Thank you

Thank you

[#6]

@bbsin 

HSTACK stacks columns horizontally.

e.g if you want to include column A and column F, you can use
=HSTACK($A:$A,$F:$F)

if you want to include column A, B and F, you can use
=HSTACK($A:$B,$F:$F)

[#7]

hi Rachel,
Great it works.

. One last question, the text are display as a string of text, is there anyway to break it into next row
event -bold title
confirmed
XXX
add Status :  the data fr the list?

Also anyway to color format the item by it Category?  Thanks

Thank you

[#8]

@bbsin 

I think you will have to change the "Dynamic Calendar" if you want to break event information into different rows.

I attached an example.

As for colouring, you can use conditional formatting to do that:

 

[#9]

@rachel 

I have tried, but it's not working
How to link it back to the year and month to pull the data, do I still need to use TEXT join?

[#10]

@bbsin 

I added Named Range for Year and Month and updated the formula too:

 

You don't need TEXTJOIN now. as Event information are displayed in 3 rows using TRANSPOSE.