• 412K Members
• 5,142 Online
• 468K Conversations
SOLVED

Contributor

# Creating a calendar that updates automatically?

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

# Re: Creating a calendar that updates automatically?

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

# Re: Creating a calendar that updates automatically?

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

Thankyou!

# Re: Creating a calendar that updates automatically?

@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.

# Re: Creating a calendar that updates automatically?

Thanks!!!

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies