Calendar on Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2281850%22%20slang%3D%22en-US%22%3ECalendar%20on%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2281850%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20create%20a%20master%20event%20calendar%20in%20Excel.%20I%20have%20it%20at%20the%20point%20where%20it%20can%20pull%20up%20to%204%20rows%20of%20events%20per%20month%20using%20the%20default%20formula%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFERROR(INDEX(tblEvents%2CSMALL(IF(Dates%3DL%246%2CROW(Dates))%2CROW(1%3A1))-TableRowStart%2C4)%2C%22%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20now%20need%20to%20add%20a%20way%20to%20format%20this%20so%20that%20if%20the%20data%20column%20labeled%20Status%20says%20Cancelled%2C%20it%20changes%20the%20font%20or%20cell%20color%20to%20red.%3C%2FP%3E%3CP%3EIt%20would%20be%20difficult%20for%20me%20to%20re-build%20this%20as%20I%20have%20over%202200%20rows%20of%20data%20in%20here%2C%20with%20around%2016%20columns.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESample%20Calendar%20-%20I%20want%20Coachella%20to%20show%20a%20different%20color%20since%20it%20was%20a%20cancelled%20event.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22sample%20calendar.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F274791i59BF605DCF3EA372%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22sample%20calendar.jpg%22%20alt%3D%22sample%20calendar.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESample%20Data%20tab%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22data.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F274792iB98C13ECECEAB6EC%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22data.jpg%22%20alt%3D%22data.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2281850%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2302628%22%20slang%3D%22en-US%22%3ERe%3A%20Calendar%20on%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2302628%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Bthat%20wouldn't%20work%20because%20we%20need%20the%20calendar%20to%20have%20a%20clean%20look%20and%20adding%20even%20C%20in%20front%20of%20it%20makes%20it%20a%20little%20messier%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2282176%22%20slang%3D%22en-US%22%3ERe%3A%20Calendar%20on%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282176%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1034207%22%20target%3D%22_blank%22%3E%40ktolani%3C%2FA%3E%26nbsp%3BFrom%20what%20you%20describe%2C%20it%20could%20be%20easiest%20to%20edit%20the%20event%20name%20and%20add%20%22CANCELLED%22%20(or%20whatever%20code%20you%20want%20to%20use)%20to%20it.%20Then%2C%20on%20the%20calendar%20sheet%20you%20could%20use%20%3CEM%3Econditional%20formatting%3C%2FEM%3E%20to%20turn%20all%20cells%20containing%20the%20word%20%22CANCELLED%22%20red.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I'm trying to create a master event calendar in Excel. I have it at the point where it can pull up to 4 rows of events per month using the default formula: 

=IFERROR(INDEX(tblEvents,SMALL(IF(Dates=L$6,ROW(Dates)),ROW(1:1))-TableRowStart,4),"")

 

I now need to add a way to format this so that if the data column labeled Status says Cancelled, it changes the font or cell color to red.

It would be difficult for me to re-build this as I have over 2200 rows of data in here, with around 16 columns. 

 

Sample Calendar - I want Coachella to show a different color since it was a cancelled event. 

sample calendar.jpg

 

Sample Data tab data.jpg

2 Replies

@ktolani From what you describe, it could be easiest to edit the event name and add "CANCELLED" (or whatever code you want to use) to it. Then, on the calendar sheet you could use conditional formatting to turn all cells containing the word "CANCELLED" red.

@Riny_van_Eekelen that wouldn't work because we need the calendar to have a clean look and adding even C in front of it makes it a little messier