Looking for Formula

[HSAR]

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.

 

[HSAR]

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.

 

[HSAR]

Team Chandoo requested for a solution.

 

[bosco_yip]

Try,

See attached file

Regards

 

[HSAR]

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.

 

[bosco_yip]

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

 

[Peter Bartholomew]

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.

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

 

[HSAR]

Thank you so much you guys are awesome Thank you.

 

[HSAR]

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]

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

 

[HSAR]

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

 

[HSAR]

When i select white then Select B5:H5 >> Font >> Format cell >> Choose "White" color >>OK >> black formatted dates are hide

 

[HSAR]

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.

 

[HSAR]

I mean to say that If Col A has similar dates then both same dates data data should be appear in under calendar date.

 

[bosco_yip]

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

 

[HSAR]

Then please update the Conditional formatting in my recent attached sheet, i have added its not working.

 

[Peter Bartholomew]

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.

= 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.

 

[HSAR]

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.

 

[Peter Bartholomew]

This should illustrate the difference.
Without filter ...


and with filter

 

[HSAR]

Yeah it seems that you have made what i was looking for. But i would have to update the office version to use it.

 

[Peter Bartholomew]

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.

 

[HSAR]

I would literally very appreciated you hard work thanks.

 

[bosco_yip]

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



Regards

 

[Peter Bartholomew]

@bosco_yip Congratulations. There appear to be minor issues with the calendar calculation linking back to version 1 of your workbook but the new functionality seems to be working well. Building the return strings in a helper range would seem to be a good idea.

 

[HSAR]

@bosco_yip Thank you Sir I wonder that when i would be expert like you. Thank you.