Forum Discussion

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 30, 2019

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.

  • Lisa LaCombe's avatar
    Lisa LaCombe
    Copper Contributor
    May 31, 2019

    SergeiBaklan PeterBartholomew1 

    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. 

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      May 31, 2019

      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.

Resources