I have inherited a spreadsheet with a calendar and task function still used by many people in the organization. I'm told that the original owner back in 2009 got the idea from a Chandoo post. Certainly the appearance is elegant (xl2bb doesn't give all the elegant formatting) and it's rather simple to operate.
The issue is that after a user clicks the mouse on a date s/he has to press F9 to have the list of tasks on the right populate from the tblTasks table. Users sometimes forget of course, then we get support calls. I'd like to have a macro-free solution where clicking into the cell will update the task list without pressing F9... hopefully without tearing down and rebuilding the whole thing. I'm hoping versions of Excel after 2007 have a workaround... just so I can punt the problem down to the next person in a couple of years!
The sample sheet is shown below (sorry, xl2bb won't support the whole calendar array). When the user clicks on a date cell, AA1 has
to give the address of the cell the user clicked. Pressing F9 will update the array formula in the tasks box on the right using the date in AA1.
And here is the tblTasks table, simplified.
The issue is that after a user clicks the mouse on a date s/he has to press F9 to have the list of tasks on the right populate from the tblTasks table. Users sometimes forget of course, then we get support calls. I'd like to have a macro-free solution where clicking into the cell will update the task list without pressing F9... hopefully without tearing down and rebuilding the whole thing. I'm hoping versions of Excel after 2007 have a workaround... just so I can punt the problem down to the next person in a couple of years!
The sample sheet is shown below (sorry, xl2bb won't support the whole calendar array). When the user clicks on a date cell, AA1 has
Excel Formula:
=ADDRESS(@CELL("row"),@CELL("col"))
Excel Formula:
=IF(INDIRECT($AA$1)<41000,"",IFERROR(INDEX(tblTasks, SMALL(IF(ISNUMBER(SEARCH(INDIRECT($AA$1), tblTasks)), ROW(tblTasks)-MIN(ROW(tblTasks))+1, ""), ROW(tblTasks)-MIN(ROW(tblTasks))+1), COLUMN(tblTasks)-MIN(COLUMN(tblTasks))+1), ""))| MyCal.xlsx | ||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | |||
| 1 | $A$9 | |||||||||||||||||||||||||||||
| 2 | 2024 | Click date, then press F9 to retrieve tasks | ||||||||||||||||||||||||||||
| 3 | Tasks Due for | Owner | ||||||||||||||||||||||||||||
| 4 | January | February | March | |||||||||||||||||||||||||||
| 5 | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Sat | |||||||||
| 6 | 31 | 1 | 2 | 3 | 4 | 5 | 6 | 28 | 29 | 30 | 31 | 1 | 2 | 3 | 25 | 26 | 27 | 28 | 29 | 1 | 2 | |||||||||
| 7 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | |||||||||
| 8 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | |||||||||
| 9 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | |||||||||
| 10 | 28 | 29 | 30 | 31 | 1 | 2 | 3 | 25 | 26 | 27 | 28 | 29 | 1 | 2 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | |||||||||
| 11 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 31 | 1 | 2 | 3 | 4 | 5 | 6 | |||||||||
| 12 | ||||||||||||||||||||||||||||||
| 13 | April | May | June | |||||||||||||||||||||||||||
| 14 | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Sat | |||||||||
| 15 | 31 | 1 | 2 | 3 | 4 | 5 | 6 | 28 | 29 | 30 | 1 | 2 | 3 | 4 | 26 | 27 | 28 | 29 | 30 | 31 | 1 | Today | ||||||||
| 16 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | |||||||||
| 17 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | Holidays | ||||||||
| 18 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | Celebrate New Year's Day | Mon Jan 01 | |||||||
| 19 | 28 | 29 | 30 | 1 | 2 | 3 | 4 | 26 | 27 | 28 | 29 | 30 | 31 | 1 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | MLK Day | Mon Jan 15 | |||||||
| 20 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 30 | 1 | 2 | 3 | 4 | 5 | 6 | President's Day | Mon Feb 19 | |||||||
| 21 | Easter | Sun Mar 31 | ||||||||||||||||||||||||||||
Calendar | ||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| AA1 | AA1 | =ADDRESS(CELL("row"),CELL("col")) |
| AA3 | AA3 | ="Tasks Due for " & IF(INDIRECT(AA1)>41000,TEXT(INDIRECT(AA1),"mmm dd"),"") |
| B4 | B4 | =DATE($J$2,1,1) |
| J4 | J4 | =DATE($J$2,2,1) |
| R4 | R4 | =DATE($J$2,3,1) |
| AA4:AB13 | AA4 | =IF(INDIRECT($AA$1)<41000,"",IFERROR(INDEX(tblTasks, SMALL(IF(ISNUMBER(SEARCH(INDIRECT($AA$1), tblTasks)), ROW(tblTasks)-MIN(ROW(tblTasks))+1, ""), ROW(tblTasks)-MIN(ROW(tblTasks))+1), COLUMN(tblTasks)-MIN(COLUMN(tblTasks))+1), "")) |
| B6 | B6 | =$B$4-WEEKDAY($B$4,17)+1 |
| C6:H11,S15:X20,K15:P20,C15:H20,S6:X11,K6:P11 | C6 | =B6+1 |
| B7:B11,R16:R20,J16:J20,B16:B20,R7:R11,J7:J11 | B7 | =H6+1 |
| J6 | J6 | =$J$4-WEEKDAY($J$4,17)+1 |
| R6 | R6 | =$R$4-WEEKDAY($R$4,17)+1 |
| B13 | B13 | =DATE($J$2,4,1) |
| J13 | J13 | =DATE($J$2,5,1) |
| R13 | R13 | =DATE($J$2,6,1) |
| B15 | B15 | =$B$13-WEEKDAY($B$13,17)+1 |
| J15 | J15 | =$J$13-WEEKDAY($J$13,17)+1 |
| R15 | R15 | =$R$13-WEEKDAY($R$13,17)+1 |
| AB18 | AB18 | =IF(WEEKDAY(DATE($J$2,1,1))=1,DATE($J$2,1,1)+1,IF(WEEKDAY(DATE($J$2,1,1))=7,DATE($J$2,1,1)+2,DATE($J$2,1,1))) |
| AB19 | AB19 | =DATE(($J$2),1,CHOOSE(WEEKDAY(DATE(($J$2),1,1)),16,15,21,20,19,18,17)) |
| AB20 | AB20 | =DATE(($J$2),2,CHOOSE(WEEKDAY(DATE(($J$2),2,1)),16,15,21,20,19,18,17)) |
| AB21 | AB21 | =FLOOR("5/"&DAY(MINUTE($J$2/38)/2+56)&"/"&$J$2,7)-34 |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| R15:X20 | Expression | =MONTH($R$13)<>MONTH(R15) | text | YES |
| J15:P20 | Expression | =MONTH($J$13)<>MONTH(J15) | text | YES |
| B15:H20 | Expression | =MONTH($B$13)<>MONTH(B15) | text | YES |
| R6:X11 | Expression | =MONTH($R$4)<>MONTH(R6) | text | YES |
| J6:P11 | Expression | =MONTH($J$4)<>MONTH(J6) | text | YES |
| B6:H11 | Expression | =MONTH($B$4)<>MONTH(B6) | text | YES |
| B6:H11 | Expression | =B6<TODAY() | text | NO |
| B6:H11 | Expression | =B6=TODAY() | text | NO |
| B6:H11 | Expression | =COUNTIF($AB$32:$AB$38,B6)>0 | text | NO |
| B6:H11 | Expression | =COUNTIF($AB$18:$AB$29,B6)>0 | text | NO |
| B6:H11 | Expression | =COUNTIFS(INDIRECT("tblTasks[Completion Date]"),B6, INDIRECT("tblTasks[Milestone]"),"=Y")>0 | text | NO |
| B6:H11 | Expression | =COUNTIF(INDIRECT("tblTasks[Completion Date]"),B6)>0 | text | NO |
| J6:P11 | Expression | =J6<TODAY() | text | NO |
| J6:P11 | Expression | =J6=TODAY() | text | NO |
| J6:P11 | Expression | =COUNTIF($AB$32:$AB$38,J6)>0 | text | NO |
| J6:P11 | Expression | =COUNTIF($AB$18:$AB$29,J6)>0 | text | NO |
| J6:P11 | Expression | =COUNTIFS(INDIRECT("tblTasks[Completion Date]"),J6, INDIRECT("tblTasks[Milestone]"),"=Y")>0 | text | NO |
| J6:P11 | Expression | =COUNTIF(INDIRECT("tblTasks[Completion Date]"),J6)>0 | text | NO |
| R6:X11 | Expression | =R6<TODAY() | text | NO |
| R6:X11 | Expression | =R6=TODAY() | text | NO |
| R6:X11 | Expression | =COUNTIF($AB$32:$AB$38,R6)>0 | text | NO |
| R6:X11 | Expression | =COUNTIF($AB$18:$AB$29,R6)>0 | text | NO |
| R6:X11 | Expression | =COUNTIFS(INDIRECT("tblTasks[Completion Date]"),R6, INDIRECT("tblTasks[Milestone]"),"=Y")>0 | text | NO |
| R6:X11 | Expression | =COUNTIF(INDIRECT("tblTasks[Completion Date]"),R6)>0 | text | NO |
| B15:H20 | Expression | =B15<TODAY() | text | NO |
| B15:H20 | Expression | =B15=TODAY() | text | NO |
| B15:H20 | Expression | =COUNTIF($AB$32:$AB$38,B15)>0 | text | NO |
| B15:H20 | Expression | =COUNTIF($AB$18:$AB$29,B15)>0 | text | NO |
| B15:H20 | Expression | =COUNTIFS(INDIRECT("tblTasks[Completion Date]"),B15, INDIRECT("tblTasks[Milestone]"),"=Y")>0 | text | NO |
| B15:H20 | Expression | =COUNTIF(INDIRECT("tblTasks[Completion Date]"),B15)>0 | text | NO |
| J15:P20 | Expression | =J15<TODAY() | text | NO |
| J15:P20 | Expression | =J15=TODAY() | text | NO |
| J15:P20 | Expression | =COUNTIF($AB$32:$AB$38,J15)>0 | text | NO |
| J15:P20 | Expression | =COUNTIF($AB$18:$AB$29,J15)>0 | text | NO |
| J15:P20 | Expression | =COUNTIFS(INDIRECT("tblTasks[Completion Date]"),J15, INDIRECT("tblTasks[Milestone]"),"=Y")>0 | text | NO |
| J15:P20 | Expression | =COUNTIF(INDIRECT("tblTasks[Completion Date]"),J15)>0 | text | NO |
| R15:X20 | Expression | =R15<TODAY() | text | NO |
| R15:X20 | Expression | =R15=TODAY() | text | NO |
| R15:X20 | Expression | =COUNTIF($AB$32:$AB$38,R15)>0 | text | NO |
| R15:X20 | Expression | =COUNTIF($AB$18:$AB$29,R15)>0 | text | NO |
| R15:X20 | Expression | =COUNTIFS(INDIRECT("tblTasks[Completion Date]"),R15, INDIRECT("tblTasks[Milestone]"),"=Y")>0 | text | NO |
| R15:X20 | Expression | =COUNTIF(INDIRECT("tblTasks[Completion Date]"),R15)>0 | text | NO |
And here is the tblTasks table, simplified.
| MyCal.xlsx | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 6 | Task | Owner | Completion Date | Milestone | ||
| 7 | Wife's Birthday | Me | 5-Mar-15 | Y | ||
| 8 | Mom's Birthday | Me | 15-Jul-15 | |||
| 9 | Boss's Birthday | Me | 11-Aug-24 | |||
| 10 | Henderson Contract Due | Boss | 13-Apr-24 | Y | ||
| 11 | Kitchen Remodel | Me | 25-Jun-24 | |||
| 12 | Cook Dinner | Wife | 25-Jun-24 | |||
CalData | ||||||
