After lots of trial and errors modifying the IF function in the Last Clicked column of the first sheet, I managed to finally the solve the problem with the following formulas in the worksheet provided below. Since no one was able to provide a solution, I would like to share my own that I managed to find today.
Account.xlsx |
---|
|
---|
| A | B | C | D | E | F | G | H |
---|
1 | Username | 4/22/2021 | 4/23/2021 | 4/24/2021 | 4/25/2021 | 4/26/2021 | 4/27/2021 | 4/28/2021 |
---|
2 | R96931932 | 24 | 28 | 31 | 31 | 31 | 31 | 31 |
---|
3 | R318278325 | 0 | 0 | 4 | 4 | 8 | 8 | 8 |
---|
4 | R467763493 | 33 | 33 | 33 | 33 | 36 | 36 | 36 |
---|
5 | R657831106 | 16 | 20 | 20 | 24 | 24 | 24 | 28 |
---|
6 | R817966441 | 12 | 20 | 20 | 24 | 24 | 24 | 24 |
---|
7 | R869099058 | 42 | 42 | 42 | 42 | 46 | 49 | 52 |
---|
|
---|
The formula I had to use to dynamically update Last Clicked column is
=IF(AND(OFFSET(RRClickspt2!$A$1,0,MATCH(TODAY(),dates,0),1,1)=TODAY(),INDEX(RRClickspt2!2:2,,MATCH(TODAY(),dates,0)+1)-INDEX(RRClickspt2!2:2,,MATCH(TODAY(),dates,0))>0),"TODAY",IF(AND(OFFSET(RRClickspt2!$A$1,0,MATCH(TODAY(),dates,0),1,1)=TODAY(),INDEX(RRClickspt2!2:2,,MATCH(TODAY(),dates,0))-INDEX(RRClickspt2!2:2,,MATCH(TODAY(),dates,0)-1)>0),"YESTERDAY",INDEX(RRClickspt2!$1:$1,MATCH(LARGE(RRClickspt2!2:2,COUNTIF(RRClickspt2!2:2,MAX(RRClickspt2!2:2))+1),RRClickspt2!2:2,2)+1)))
Yeah very lengthy one, but that's because I had to make sure any one of the three conditions are satisfied i.e, if any clicks are made today, it should display TODAY, else YESTERDAY, else the date someone last clicked. The INDEX and MATCH combination played a big role here to refer cells dynamically based on today's date. Here in the formula. 'dates' is simply the array name I gave from the 1st row in the second sheet excluding the cell A1 i.e., Username.