• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Looking for Formula

HSAR

New Member
Trying to make a formula that met to a condition IF calendar date = Event Schedule date i.e column A then all row details should be appear in under calender date cell.

If possible that ‘Event Schedule Tab is filtered, can this be reflected in dynamic calendar so on events on the filtered list appear in calendar. E.g. If Colum F is filtered by Brazil, can only Brazil Events appear on dynamic calendar.

here is attached file.

=IF(TEXT(C8,"MM/DD/YYYY")=TEXT('Event Schedule'!A4,"MM/DD/YYYY"),CONCATENATE(TEXT('Event Schedule'!B4,"hh:mm:ss AM/PM")," ",'Event Schedule'!C4," ",'Event Schedule'!D4," ",'Event Schedule'!E4," ",'Event Schedule'!F4," ",'Event Schedule'!G4,""))

using this formulla but it is not seems to perfect.
 

Attachments

  • Sports event Spreadsheet.xlsx
    66 KB · Views: 5
Last edited:
Can someone please provide the solution.

=IF(C8='Event Schedule'!A4,CONCATENATE(TEXT('Event Schedule'!B4,"hh:mm:ss AM/PM")," ",'Event Schedule'!C4," ",'Event Schedule'!D4," ",'Event Schedule'!E4," ",'Event Schedule'!F4," ",'Event Schedule'!G4),"")&
IF(C8='Event Schedule'!A5,CONCATENATE(TEXT('Event Schedule'!B5,"hh:mm:ss AM/PM")," ",'Event Schedule'!C5," ",'Event Schedule'!D5," ",'Event Schedule'!E5," ",'Event Schedule'!F5," ",'Event Schedule'!G5),"")&
IF(C8='Event Schedule'!A6,CONCATENATE(TEXT('Event Schedule'!B6,"hh:mm:ss AM/PM")," ",'Event Schedule'!C6," ",'Event Schedule'!D6," ",'Event Schedule'!E6," ",'Event Schedule'!F6," ",'Event Schedule'!G6),"")&
IF(C8='Event Schedule'!A7,CONCATENATE(TEXT('Event Schedule'!B7,"hh:mm:ss AM/PM")," ",'Event Schedule'!C7," ",'Event Schedule'!D7," ",'Event Schedule'!E7," ",'Event Schedule'!F7," ",'Event Schedule'!G7),"")&
IF(C8='Event Schedule'!A8,CONCATENATE(TEXT('Event Schedule'!B8,"hh:mm:ss AM/PM")," ",'Event Schedule'!C8," ",'Event Schedule'!D8," ",'Event Schedule'!E8," ",'Event Schedule'!F8," ",'Event Schedule'!G8),"")&
IF(C8='Event Schedule'!A9,CONCATENATE(TEXT('Event Schedule'!B9,"hh:mm:ss AM/PM")," ",'Event Schedule'!C9," ",'Event Schedule'!D9," ",'Event Schedule'!E9," ",'Event Schedule'!F9," ",'Event Schedule'!G9),"")&
IF(C8='Event Schedule'!A10,CONCATENATE(TEXT('Event Schedule'!B10,"hh:mm:ss AM/PM")," ",'Event Schedule'!C10," ",'Event Schedule'!D10," ",'Event Schedule'!E10," ",'Event Schedule'!F10," ",'Event Schedule'!G10),"")

i have make this but it cannot cover 100+ rows.


Any possibility is available if that ‘Event Schedule Tab is filtered, can this be reflected in dynamic calendar so on events on the filtered list appear in calendar. E.g. If Colum F is filtered by Brazil, can only Brazil Events appear on dynamic calendar.
 
Last edited:
bosco_yip


Thank you veryyy much,


There is one issue that if there are 2 or more dates in Event Sheet ColA then 2 or more sheet data should appear in under calendar date.

i also requested that please make a filter for below activity.

If possible that ‘Event Schedule Tab is filtered, can this be reflected in dynamic calendar so on events on the filtered list appear in calendar. E.g. If Column F is filtered by Brazil, can only Brazil Events appear on dynamic calendar.
 
Last edited:
bosco_yip


Thank you veryyy much,


There is one issue that if there are 2 or more dates in Event Sheet ColA then 2 or more sheet data should appear in under calendar date.

i also requested that please make a filter for below activity.

If possible that ‘Event Schedule Tab is filtered, can this be reflected in dynamic calendar so on events on the filtered list appear in calendar. E.g. If Column F is filtered by Brazil, can only Brazil Events appear on dynamic calendar.
Hi,

Please see revised file with adding Conditional Formatting to the calendar table.

If column F is filtered by Brazil, only Brazil events appear. And if filtered by "All", all events appear.

and

All date in the Event sheet must be unique, and cannot "Duplicate". Please put all additional event in the same date.

Regards
 

Attachments

  • Sports event (BY R1).xlsx
    68.3 KB · Views: 3
Last edited:
I was going to suggest that it will not be easy to do what you require without the FILTER function from Office 365, but I expect @bosco_yip will have done just that; I will look later. The key is to search or filter the schedule sheet for each calendar day.
70166

Code:
= TEXTJOIN(CHAR(10),,
    FILTER(Schedule[Event Name]&", "&Schedule[Sport],
        (Schedule[Date]=calendarDate)*
        ((Schedule[Country]=selectedCountry)+(selectedCountry="")),
    "")
    )
 

Attachments

  • Sports event Spreadsheet (PB).xlsx
    70 KB · Views: 8
bosco_yip

I apologies to ask you again that how the filter for country has been attached to formula because i had not upload the original sheet. so i can apply it to original sheeet
 
bosco_yip
I apologies to ask you again that how the filter for country has been attached to formula because i had not upload the original sheet. so i can apply it to original sheeet

Try,

Select B5:H5 >> Font >> Format cell >> Choose "White" color >>OK >>

>> And, click "Conditional Formatting" >> new rule >> Use a formula .... >>In the rule box enter formula :
=ISNUMBER(SEARCH('Event Schedule'!$F$3,B5))+('Event Schedule'!$F$3="All")
>> Format >> Font >> Choose "black" color >> OK >> Finish

>> Then, select B5:H5 >> Copy Format and Paste to B7:H7, B9:H9…....and B15:H15 >> Finish

70169
 
Last edited:
I have tried but not working for me It would be humble request please update it.
 

Attachments

  • Sheet1.xlsx
    68.5 KB · Views: 6
When i select white then Select B5:H5 >> Font >> Format cell >> Choose "White" color >>OK >> black formatted dates are hide
 
Sir I also will need multiple events to be able to appear in each day so the calendar needs to be able to adjust for this.
 
I mean to say that If Col A has similar dates then both same dates data data should be appear in under calendar date.
 
I mean to say that If Col A has similar dates then both same dates data data should be appear in under calendar date.
Please see Peter's reply (Post # no.7) in using TEXTJOIN + FILTER dynamic function which can return multiple events for duplicate dates

My suggested VLOOKUP function can only return the 1st occurrence and cannot suit with your multiple events requirement.

Regards
 
Last edited:
Then please update the Conditional formatting in my recent attached sheet, i have added its not working.
 
Hello @HSAR
I assume you are not working with MS365 so only @bosco_yip 's solution is actually relevant to you. That said, I have continued as a way of exploring Beta release functionality in 365. May I use your file for demonstration purposes and to send a 'frown' to Microsoft' (Intellisense is no longer working with local names in the LET function and the Name 'event.count' now causes an error)?

That said, the new function makes some formulas possible which would formerly have been fiendishly difficult (though I would put nothing past @bosco_yip). My formula uses the LET function and is very long but that is, in part, because I have chosen to use long name to document the partial steps within the solution.

Code:
= LET(
   dateCriterion,  Schedule[Date]=calendarDate,
   countryCriterion, (Schedule[Country]=selectedCountry)+(selectedCountry="ALL"),
   sportCriterion,  (Schedule[Sport]=selectedSport)+(selectedSport="ALL"),
   combinedCriteria, dateCriterion*countryCriterion*sportCriterion,
   eventCount, SUM(combinedCriteria),
   description, LET(
       sport,  IF(selectedSport="ALL", Schedule[Sport] & ", ", ""),
       country, IF(selectedCountry="ALL", Schedule[Country], ""),
       fullDescription, TEXT(Schedule[Time],"hh:mm") &": "& Schedule[Event Name] &", "& sport & country,
       shortDescription, TEXT(Schedule[Time],"hh:mm") &": "& Schedule[Sport],
       IF(eventCount>1, shortDescription, fullDescription) ),
   filteredList, FILTER(description, combinedCriteria, ""),
   events, TEXTJOIN(CHAR(10),,filteredList ),
   events )

One of the things I have chosen to do is to output shorter descriptions of each event if there are multiple events on a given day. A further change is to only report the sport when the sport filter is not set, similarly for country.
 
Yes i have old version but for the time being i would request @bosco_yip to update the recent attached sheet conditional formatting for working afterwards i will update the office version to use your formula's.
 
Yeah it seems that you have made what i was looking for. But i would have to update the office version to use it.
 
Not only an update, you would need to set it to work on the insider/beta channel to use this last solution.
Also, whilst I have restricted myself to defined names (no direct cell referencing) and used array formulas wherever possible for many years now, this is far from a traditional Excel way of working; you would need to consider how you will maintain the solution.

There are still efficiency gains to be had by refactoring, though they are not needed at the moment. For example, is it possible to filter first and only then concatenate the relevant output. If there are no events on a given day, no further calculation is needed. I am still learning how to handle the new functionality for best results.
 
Yeah it seems that you have made what i was looking for. But i would have to update the office version to use it.
Hi,

Here is a helper column formula proposal for those do not have Office 365, in return multiple events with duplicate dates.

And, the "Calendar table" complete with "Conditional Formatting" for country selection in the "Event Schedule" Sheet F3

70197

Regards
 

Attachments

  • Sports event (BY R2).xlsx
    73.5 KB · Views: 12
Last edited:
Back
Top