May 30 2019 06:41 AM
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?
May 30 2019 02:05 PM
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.
May 30 2019 03:24 PM - edited May 30 2019 03:28 PM
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)
May 31 2019 11:19 AM
@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.
May 31 2019 12:50 PM - edited May 31 2019 12:56 PM
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.
May 31 2019 02:47 PM - edited May 31 2019 03:01 PM
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!
May 31 2019 03:01 PM
Peter, they are in one line here and separated with "/"
Jun 02 2019 01:49 AM
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 |
Jun 03 2019 01:04 AM
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,""))