Update formula on mouse click without macro

M

macfuller

Active Member
Joined
Apr 30, 2014
Messages
319
Office Version
  1. 365
Platform
  1. Windows
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
Excel Formula: 
=ADDRESS(@CELL("row"),@CELL("col"))
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.

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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1$A$9
22024Click date, then press F9 to retrieve tasks
3Tasks Due for Owner
4JanuaryFebruaryMarch  
5SunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSat  
63112345628293031123252627282912  
778910111213456789103456789  
8141516171819201112131415161710111213141516  
9212223242526271819202122232417181920212223  
102829303112325262728291224252627282930  
1145678910345678931123456  
12  
13AprilMayJune  
14SunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSat
153112345628293012342627282930311Today
16789101112135678910112345678
1714151617181920121314151617189101112131415Holidays
18212223242526271920212223242516171819202122Celebrate New Year's DayMon Jan 01
192829301234262728293031123242526272829MLK DayMon Jan 15
20567891011234567830123456President's DayMon Feb 19
21EasterSun Mar 31
Calendar
Cell Formulas
RangeFormula
AA1AA1=ADDRESS(CELL("row"),CELL("col"))
AA3AA3="Tasks Due for " & IF(INDIRECT(AA1)>41000,TEXT(INDIRECT(AA1),"mmm dd"),"")
B4B4=DATE($J$2,1,1)
J4J4=DATE($J$2,2,1)
R4R4=DATE($J$2,3,1)
AA4:AB13AA4=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), ""))
B6B6=$B$4-WEEKDAY($B$4,17)+1
C6:H11,S15:X20,K15:P20,C15:H20,S6:X11,K6:P11C6=B6+1
B7:B11,R16:R20,J16:J20,B16:B20,R7:R11,J7:J11B7=H6+1
J6J6=$J$4-WEEKDAY($J$4,17)+1
R6R6=$R$4-WEEKDAY($R$4,17)+1
B13B13=DATE($J$2,4,1)
J13J13=DATE($J$2,5,1)
R13R13=DATE($J$2,6,1)
B15B15=$B$13-WEEKDAY($B$13,17)+1
J15J15=$J$13-WEEKDAY($J$13,17)+1
R15R15=$R$13-WEEKDAY($R$13,17)+1
AB18AB18=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)))
AB19AB19=DATE(($J$2),1,CHOOSE(WEEKDAY(DATE(($J$2),1,1)),16,15,21,20,19,18,17))
AB20AB20=DATE(($J$2),2,CHOOSE(WEEKDAY(DATE(($J$2),2,1)),16,15,21,20,19,18,17))
AB21AB21=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
CellConditionCell FormatStop If True
R15:X20Expression=MONTH($R$13)<>MONTH(R15)textYES
J15:P20Expression=MONTH($J$13)<>MONTH(J15)textYES
B15:H20Expression=MONTH($B$13)<>MONTH(B15)textYES
R6:X11Expression=MONTH($R$4)<>MONTH(R6)textYES
J6:P11Expression=MONTH($J$4)<>MONTH(J6)textYES
B6:H11Expression=MONTH($B$4)<>MONTH(B6)textYES
B6:H11Expression=B6<TODAY()textNO
B6:H11Expression=B6=TODAY()textNO
B6:H11Expression=COUNTIF($AB$32:$AB$38,B6)>0textNO
B6:H11Expression=COUNTIF($AB$18:$AB$29,B6)>0textNO
B6:H11Expression=COUNTIFS(INDIRECT("tblTasks[Completion Date]"),B6, INDIRECT("tblTasks[Milestone]"),"=Y")>0textNO
B6:H11Expression=COUNTIF(INDIRECT("tblTasks[Completion Date]"),B6)>0textNO
J6:P11Expression=J6<TODAY()textNO
J6:P11Expression=J6=TODAY()textNO
J6:P11Expression=COUNTIF($AB$32:$AB$38,J6)>0textNO
J6:P11Expression=COUNTIF($AB$18:$AB$29,J6)>0textNO
J6:P11Expression=COUNTIFS(INDIRECT("tblTasks[Completion Date]"),J6, INDIRECT("tblTasks[Milestone]"),"=Y")>0textNO
J6:P11Expression=COUNTIF(INDIRECT("tblTasks[Completion Date]"),J6)>0textNO
R6:X11Expression=R6<TODAY()textNO
R6:X11Expression=R6=TODAY()textNO
R6:X11Expression=COUNTIF($AB$32:$AB$38,R6)>0textNO
R6:X11Expression=COUNTIF($AB$18:$AB$29,R6)>0textNO
R6:X11Expression=COUNTIFS(INDIRECT("tblTasks[Completion Date]"),R6, INDIRECT("tblTasks[Milestone]"),"=Y")>0textNO
R6:X11Expression=COUNTIF(INDIRECT("tblTasks[Completion Date]"),R6)>0textNO
B15:H20Expression=B15<TODAY()textNO
B15:H20Expression=B15=TODAY()textNO
B15:H20Expression=COUNTIF($AB$32:$AB$38,B15)>0textNO
B15:H20Expression=COUNTIF($AB$18:$AB$29,B15)>0textNO
B15:H20Expression=COUNTIFS(INDIRECT("tblTasks[Completion Date]"),B15, INDIRECT("tblTasks[Milestone]"),"=Y")>0textNO
B15:H20Expression=COUNTIF(INDIRECT("tblTasks[Completion Date]"),B15)>0textNO
J15:P20Expression=J15<TODAY()textNO
J15:P20Expression=J15=TODAY()textNO
J15:P20Expression=COUNTIF($AB$32:$AB$38,J15)>0textNO
J15:P20Expression=COUNTIF($AB$18:$AB$29,J15)>0textNO
J15:P20Expression=COUNTIFS(INDIRECT("tblTasks[Completion Date]"),J15, INDIRECT("tblTasks[Milestone]"),"=Y")>0textNO
J15:P20Expression=COUNTIF(INDIRECT("tblTasks[Completion Date]"),J15)>0textNO
R15:X20Expression=R15<TODAY()textNO
R15:X20Expression=R15=TODAY()textNO
R15:X20Expression=COUNTIF($AB$32:$AB$38,R15)>0textNO
R15:X20Expression=COUNTIF($AB$18:$AB$29,R15)>0textNO
R15:X20Expression=COUNTIFS(INDIRECT("tblTasks[Completion Date]"),R15, INDIRECT("tblTasks[Milestone]"),"=Y")>0textNO
R15:X20Expression=COUNTIF(INDIRECT("tblTasks[Completion Date]"),R15)>0textNO


And here is the tblTasks table, simplified.
MyCal.xlsx
ABCD
6TaskOwnerCompletion DateMilestone
7Wife's BirthdayMe5-Mar-15Y
8Mom's BirthdayMe15-Jul-15
9Boss's BirthdayMe11-Aug-24
10Henderson Contract DueBoss13-Apr-24Y
11Kitchen RemodelMe25-Jun-24
12Cook DinnerWife25-Jun-24
CalData
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Excel cannot detect a change in the active cell, you would need to use a macro, or keep what you have already got.
 
Upvote 0
Solution
Thanks. I guess we'll have to wait until we all get Slack or Teams to retire this.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
conditional formatting based on duplicate cells
Replies
4
Views
253
Joe4
Joe4
J
  • Question Question
copying adjacent cells based on value of referenced cell
Replies
4
Views
256
arun_eagertolearn
A
W
  • Question Question
Copy formula across 28 day dynamic shift calendar
Replies
4
Views
334
Wolfgang17
W
J
  • Solved
returning a value if multiple criteria are true
Replies
3
Views
394
etaf
etaf
J
  • Question Question
conditional formatting based on adjacent cells matching
Replies
1
Views
260
etaf
etaf

Forum statistics

Threads
1,223,886
Messages
6,175,195
Members
452,616
Latest member
intern444

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top