Forum Discussion
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
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, SergeiBaklan
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.
Thanks!!!
