How to update formula for the next day by changing cell reference automatically?

[greenpower78]

I am trying to tackle a problem of updating a formula dynamically based on time. Suppose if a formula contains cell reference to a cell containing today's date, the cell reference should change automatically to the next adjacent cell, say to the right, containing tomorrow's date the next day. I don't want to manually change the cell reference all the time if I were to achieve automation. Is there a solution?

 

[Joe4]

In cases like this, seeing a sample of data and what you want to happen are usually most helpful (as data structure is critical to these questions). Could you post an example?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[greenpower78]

Okay, here are the mini-sheets for your reference. What I am trying to do is I want the 'Last Clicked' column from the sheet 'RRClickspt1' to update whenever any clicks are made. If a user made a click today, the Last Clicked column cell will display "TODAY" for the corresponding username in the first sheet. The algorithm I used for determining if any clicks were made is to see if there is any difference between the clicks made today and the previous day for the corresponding user in the sheet 'RRClickspt2'. The date row is in the second sheet and each date column contains the total clicks made by each user and the total clicks are calculated in cumulative manner.

Similarly, if a user made clicks not today, but yesterday. I want the Last Clicked column's cell to display YESTERDAY. Finally if the user doesn't make any clicks today or made clicks yesterday, I want the cell to display the date on which the user last clicked. The number of total clicks made is manually inputted for each user.

Account.xlsx
	A	B	C	D
4	Username	Referral Since (Real)	Last Clicked	Avg. Clicks
5	R96931932	4/8/21 12:10 PM	FALSE	1.632
6	R318278325	4/23/21 1:13 AM	YESTERDAY	2.000
7	R467763493	3/31/21 9:38 PM	YESTERDAY	1.385
8	R657831106	4/8/21 12:10 PM	FALSE	1.263
9	R817966441	4/8/21 12:10 PM	FALSE	1.263
10	R869099058	3/31/21 9:38 PM	TODAY	1.885
RRClickspt1

Cell Formulas
Range	Formula
C5:C10	C5	=IF(AND(RRClickspt2!G$1=TODAY(),RRClickspt2!G2-RRClickspt2!F2>0),"TODAY",IF(AND(RRClickspt2!G$1=TODAY(),RRClickspt2!F2-RRClickspt2!E2>0),"YESTERDAY"))
D5:D10	D5	=MAX(OFFSET(RRClickspt2!$A2,0,1,1,COUNT(RRClickspt2!2:2)))/INT(NOW()-$B5)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D5:D10	Cell Value	>$B$2	text	NO
D5:D10	Cell Value	=$B$2	text	NO
D5:D10	Cell Value	<$B$2	text	NO

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
3	R318278325	0	0	4	4	8	8
4	R467763493	33	33	33	33	36	36
5	R657831106	16	20	20	24	24	24
6	R817966441	12	20	20	24	24	24
7	R869099058	42	42	42	42	46	49
RRClickspt2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:AC1,B2:H7	Expression	=MOD(COLUMNS($B:B),7)=0	text	NO

 

[greenpower78]

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
4	Username	Referral Since (Real)	Last Clicked	Avg. Clicks
5	R96931932	4/8/21 12:10 PM	4/24/21	1.550
6	R318278325	4/23/21 1:13 AM	4/26/21	1.600
7	R467763493	3/31/21 9:38 PM	4/26/21	1.286
8	R657831106	4/8/21 12:10 PM	TODAY	1.400
9	R817966441	4/8/21 12:10 PM	4/25/21	1.200
10	R869099058	3/31/21 9:38 PM	TODAY	1.857
RRClickspt1

Cell Formulas
Range	Formula
C5:C10	C5	=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)))
D5:D10	D5	=MAX(OFFSET(RRClickspt2!$A2,0,1,1,COUNT(RRClickspt2!2:2)))/INT(NOW()-$B5)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D5:D10	Cell Value	>$B$2	text	NO
D5:D10	Cell Value	=$B$2	text	NO
D5:D10	Cell Value	<$B$2	text	NO

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
RRClickspt2

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.