SOLVED
Home

Creating a calendar that updates automatically?

%3CLINGO-SUB%20id%3D%22lingo-sub-700027%22%20slang%3D%22en-US%22%3ECreating%20a%20calendar%20that%20updates%20automatically%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-700027%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20folder%20you%20can%20see%20I%20have%20a%20list%20of%20actions%20with%20a%20date%20for%20completion%20and%20their%20value%20of%20importance%2C%20and%20whether%20they've%20yet%20been%20achieved.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20this%20to%20populate%20the%20calendar%20tab%20-%20how%20could%20I%20do%20this%20so%20when%20i%20change%20the%20dates%20the%20calandar%20highlights%20that%20date%20and%20shows%20its%20value%20(I%20then%20will%20use%20a%20conditional%20format%20to%20show%20whats%20of%20most%20importance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEXTRA%20%3A%20Some%20actions%20will%20only%20happen%20if%20others%20do%20is%20there%20a%20way%20i%20could%20show%20that%20ie%20order%203%20will%20only%20happen%20if%20order%202%20is%20done%20if%20not%20then%20there%20is%20no%20need%20for%20order%20three%20to%20be%20shown%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBailey100%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-700027%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-700515%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20calendar%20that%20updates%20automatically%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-700515%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351100%22%20target%3D%22_blank%22%3E%40Bailey100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%20like%3C%2FP%3E%0A%3CPRE%3E%3DIF(%0A%20%20%20MOD(COLUMN()%2C6)%3DCOLUMN(%24B%241)%2C%0A%20%20%20IFERROR(%0A%20%20%20%20%20%20INDEX(Table1%5BValue%5D%2C%0A%20%20%20%20%20%20MATCH(DATE(2019%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20MONTH(INDEX(%241%3A%241%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20(INT((((INT((COLUMN()-COLUMN(%24B%242)%2B6)%2F6)-1)*6%2BCOLUMN(%24B%242))-COLUMN(%24B%242)%2B6)%2F6)-1)*6%2BCOLUMN(%24B%242))%26amp%3B1)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%24A3)%2C%0A%20%20%20%20%20%20Table1%5BTimeline%5D%2C0)%0A%20%20%20%20%20%20)*100%2C%0A%20%20%20%22%22)%2C%0A%22%22)%0A%3C%2FPRE%3E%0A%3CP%3EI%20inserted%20above%20for%20Jun%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-706047%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20calendar%20that%20updates%20automatically%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-706047%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20seems%20to%20work%2C%20can%20you%20explain%20the%20formula%20please%20so%20I%20can%20apply%20it%20to%20other%20months%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThankyou!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-707880%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20calendar%20that%20updates%20automatically%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-707880%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351100%22%20target%3D%22_blank%22%3E%40Bailey100%3C%2FA%3E%26nbsp%3B%2C%20in%20general%20it's%20not%20necessary%20to%20enter%20formulas%20manually%20into%20other%20cells.%20You%20may%20copy%20entire%20June%20block%20and%20paste%20it%20into%20another%20month%20block(s)%2C%20adjust%20format%20by%20Format%20Painter%20after%20that.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20formula%20do%3A%3C%2FP%3E%0A%3CP%3E-%20condition%20under%20IF%20checks%20if%20we%20stay%20on%20first%20column%20%22Awards%22%20of%20each%20month%20block%20(columns%202%2C8%2C14%2C...).%20If%20so%20we%20try%20to%20find%20the%20value%20to%20return%20into%20the%20cell%20with%20INDEX%2FMATCH%2C%20otherwise%20return%20empty%20string.%3C%2FP%3E%0A%3CP%3EINDEX%20returns%20the%20value%20of%20the%20cell%20from%20the%20column%20Values%20of%20the%20source%20table%20which%20is%20on%20the%20position%20which%20returns%20MATCH.%3C%2FP%3E%0A%3CP%3EMATCH%20returns%20such%20position%20compare%20the%20dates%20in%20Timeline%20column%20with%20the%20date%20for%20the%20current%20cell.%20If%20found%20then%20position%20returned%20else%20we%20have%20an%20error.%20The%20latest%20is%20handled%20by%20wrapped%20IFERROR%20function%20which%20returns%20empty%20string%20in%20case%20of%20error.%3C%2FP%3E%0A%3CP%3EThe%20date%20for%20the%20current%20cell%20is%20calculated%20by%20DATE(year%2C%20month%2C%20day)%20function.%3C%2FP%3E%0A%3CP%3EYear%20is%20hardcoded%20as%202019%3C%2FP%3E%0A%3CP%3EMonth%20number%20is%20returned%20by%20formula%20like%20%3DMONTH(MonthName%26amp%3B1)%2C%20e.g.%20%3DMONTH(%22Feb%22%26amp%3B1)%20returns%202.%3C%2FP%3E%0A%3CP%3EMonth%20name%20is%20picked-up%20from%20row%201%20by%20internal%20INDEX%2C%20where%20combination%20with%20COLUMN%20functions%20returns%20position%20of%20B1%2C%20H1%2C%20etc%20for%20the%20current%20month%20block.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20in%20row%201%20you%20use%20dates%20(e.g.%20first%20of%20each%20month)%20formatted%20as%20%22mmm%22%2C%20the%20formula%20will%20be%20bit%20more%20simple%20and%20much%20more%20flexible.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-711034%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20calendar%20that%20updates%20automatically%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711034%22%20slang%3D%22en-US%22%3E%3CP%3EThanks!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Bailey100
Contributor

Hi,

 

In the attached folder you can see I have a list of actions with a date for completion and their value of importance, and whether they've yet been achieved.

 

I want this to populate the calendar tab - how could I do this so when i change the dates the calandar highlights that date and shows its value (I then will use a conditional format to show whats of most importance.

 

EXTRA : Some actions will only happen if others do is there a way i could show that ie order 3 will only happen if order 2 is done if not then there is no need for order three to be shown?

 

Many thanks!

 

Bailey100

4 Replies
Solution

@Bailey100 

 

That could be like

=IF(
   MOD(COLUMN(),6)=COLUMN($B$1),
   IFERROR(
      INDEX(Table1[Value],
      MATCH(DATE(2019,
            MONTH(INDEX($1:$1,
               (INT((((INT((COLUMN()-COLUMN($B$2)+6)/6)-1)*6+COLUMN($B$2))-COLUMN($B$2)+6)/6)-1)*6+COLUMN($B$2))&1),
            $A3),
      Table1[Timeline],0)
      )*100,
   ""),
"")

I inserted above for Jun

Hi, @Sergei Baklan 

 

 

That seems to work, can you explain the formula please so I can apply it to other months?

 

Thankyou! 

 

 

@Bailey100 , in general it's not necessary to enter formulas manually into other cells. You may copy entire June block and paste it into another month block(s), adjust format by Format Painter after that.

 

What formula do:

- condition under IF checks if we stay on first column "Awards" of each month block (columns 2,8,14,...). If so we try to find the value to return into the cell with INDEX/MATCH, otherwise return empty string.

INDEX returns the value of the cell from the column Values of the source table which is on the position which returns MATCH.

MATCH returns such position compare the dates in Timeline column with the date for the current cell. If found then position returned else we have an error. The latest is handled by wrapped IFERROR function which returns empty string in case of error.

The date for the current cell is calculated by DATE(year, month, day) function.

Year is hardcoded as 2019

Month number is returned by formula like =MONTH(MonthName&1), e.g. =MONTH("Feb"&1) returns 2.

Month name is picked-up from row 1 by internal INDEX, where combination with COLUMN functions returns position of B1, H1, etc for the current month block.

 

If in row 1 you use dates (e.g. first of each month) formatted as "mmm", the formula will be bit more simple and much more flexible.