Calendar Help

[Lisa LaCombe]

I am trying to include the Name, Room, Unit, Provider, and Event Type to show on the month calendar together.  How do I do that?

[#2]

@Lisa LaCombe 

Hi Lisa,

That could be like

=IFERROR(
   VLOOKUP(D5,Important_Dates,2,FALSE) & CHAR(10) &
   VLOOKUP(D5,Important_Dates,3,FALSE) & CHAR(10) &
   VLOOKUP(D5,Important_Dates,4,FALSE) & CHAR(10) &
   VLOOKUP(D5,Important_Dates,5,FALSE) & CHAR(10) &
   VLOOKUP(D5,Important_Dates,6,FALSE) & CHAR(10) &
   VLOOKUP(D5,Important_Dates,7,FALSE) & CHAR(10),"")

see in Jun sheet.

Please note, in Daily Calendar you may have only one record for each date. If few ones, only first will be shown.

[#3]

@Lisa LaCombe

Basically the same technique as you and @Sergei Baklan but with a slightly risky edge that might invite Sergei's comment.  I do not normally use VLOOKUP but it seems as good as any alternative here 

= IFERROR( TEXTJOIN( ¶, TRUE,

VLOOKUP( Current_Date, Important_Dates, {2,3,4,5,7}, FALSE )

),"")

The main risk is in my definition of 'Current_Date' which I allowed to be relative to the active cell and sheet (immediately above it) by referring to 

=!R[-1]C

It seems to be working and allowed me to add an 'important date'.   The name '¶' is a little non-standard but it simply refers to the line feed character

=CHAR(10) 

The other point of note is that the array constant  returns an array of 5 text strings that TEXTJOIN combines (Office 2013 and later I think)

[#4]

@Sergei Baklan @Peter Bartholomew 

Mr. Baklan, your formula worked.  But now if two or more items fall on the same date, how could I get them all to show on the Jun calendar for that day?  Also, is there a way to "squish" the data down so all events for a specific day can fit in that day?  I know how to make the cells bigger if I could just get the pertinent info to fit. 

[#5]

@Lisa LaCombe 

Hi Lisa,

As variant

1) Let add two named ranges, one only for dates in Daily Calendar sheet, another one for data only (added in attached file)

2) Formula for the event could be

=TEXTJOIN("",TRUE,
   IF(ROW(Important_Data_Only)*(Important_Dates_Only=F5)*(Important_Data_Only<>""),
      Important_Data_Only &
      IF(COLUMN(Important_Data_Only)=4," / ",
         IF(COLUMN(Important_Data_Only)=8,CHAR(10) & "--------" & CHAR(10), CHAR(10))),
   ""))

Please note, TEXTJOIN() is available only for Office365 subscribers plus for Excel 2019.

I hardcoded here columns number 4 and 8, but they also could be added to named range. This part of the formula compacts a bit Room / Unit and adds separator after the event

3) On ribbon check box View->Headings (Show tab)

4) Select rows with events and Home->Format->Row Height, set 150. Alternatively AutoFit Row Height in the same drop-down menu.

5) Uncheck Headings

In attached file formulas updated for Jun only.

[#6]

@Lisa LaCombe 

If you are appending fields individually with "&" (I didn't realise TEXTJOIN was so recent; up to about a month ago I was using Office 2010 so hadn't really noticed its release date) you could reduce space demands by using ", " to separate the Room and Unit codes, so placing them on the same line.

I would probably use FILTER to select multiple records but that is little use to most others.

Correction

I have just found that TEXTJOIN accepts an array as delimiters so comma separator and line feeds can be combined!

[#7]

@Peter Bartholomew 

Peter, they are in one line here and separated with "/"

[#8]

@Sergei Baklan 

I am basically playing but I found a TEXTJOIN delimiter to be the named formula

=CHAR({10,47,47,10,10})

worked well in the formula

= IFERROR( TEXTJOIN( delimiter, TRUE,

VLOOKUP( CurrentDate, ImportantDates, {2,3,4,5,7}, FALSE ) ),

"")

Doe, Jane
128B/1B/43632
30 Day Evaluation

[#9]

@Peter Bartholomew ,

Yes, that's much better. I'd only use as delimiter

=SUBSTITUTE({"X"," / ","X","X","X--------X"},"X",CHAR(10))

not to limit delimiter by one character. Plus VLOOKUP returns only one (first found) record. Finally

=TEXTJOIN(delimeter,TRUE,
   IF(ROW(Important_Data_Only)*(Important_Dates_Only=Current_Date)*(Important_Data_Only<>""),
      Important_Data_Only,""))