# Running Totals



## itsgrady (Dec 13, 2022)

I have a spreadsheet with days of the week as the header for each column. 

What I would like to do is use the last column (7th column) for current information. The seventh column will always be the current date - the other 6 columns will be the previous days.

The next day I would like for all data move over to the left and the oldest day roll off the form. So, the current 7 days will always be on the form. 

Is there an easy formula to move everything over each day. I was thinking to use a button with VBA code, unless there is an easier way. 

Thanks for the help.


----------



## JamesCanale (Dec 13, 2022)

I'd store all the data in some TABLE somewhere and just show the last seven days.

In K2 I use today.  But I might also use what is in K1 for that instead.
MrExcelPlayground14.xlsxABCDEFGHIJK1DateValueTue 12/13/2022211/15/202286DayWed 12/7/2022Thu 12/8/2022Fri 12/9/2022Sat 12/10/2022Sun 12/11/2022Mon 12/12/2022Tue 12/13/2022311/16/202270Value90811620114090411/17/202211511/18/202223611/19/202279711/20/202289811/21/202262911/22/2022661011/23/2022881111/24/2022601211/25/2022991311/26/2022771411/27/2022751511/28/2022611611/29/2022911711/30/2022931812/1/2022571912/2/2022142012/3/2022762112/4/2022792212/5/2022722312/6/2022402412/7/2022902512/8/2022812612/9/2022162712/10/2022202812/11/2022112912/12/2022403012/13/202290Sheet28Cell FormulasRangeFormulaK1K1=MAX(Table3[Date])E2:J2E2=F2-1K2K2=TODAY()E3:K3E3=XLOOKUP(E2,Table3[[Date]:[Date]],Table3[[Value]:[Value]],"",0)


----------



## itsgrady (Dec 13, 2022)

JamesCanale said:


> I'd store all the data in some TABLE somewhere and just show the last seven days.
> 
> In K2 I use today.  But I might also use what is in K1 for that instead.
> MrExcelPlayground14.xlsxABCDEFGHIJK1DateValueTue 12/13/2022211/15/202286DayWed 12/7/2022Thu 12/8/2022Fri 12/9/2022Sat 12/10/2022Sun 12/11/2022Mon 12/12/2022Tue 12/13/2022311/16/202270Value90811620114090411/17/202211511/18/202223611/19/202279711/20/202289811/21/202262911/22/2022661011/23/2022881111/24/2022601211/25/2022991311/26/2022771411/27/2022751511/28/2022611611/29/2022911711/30/2022931812/1/2022571912/2/2022142012/3/2022762112/4/2022792212/5/2022722312/6/2022402412/7/2022902512/8/2022812612/9/2022162712/10/2022202812/11/2022112912/12/2022403012/13/202290Sheet28Cell FormulasRangeFormulaK1K1=MAX(Table3[Date])E2:J2E2=F2-1K2K2=TODAY()E3:K3E3=XLOOKUP(E2,Table3[[Date]:[Date]],Table3[[Value]:[Value]],"",0)


I like that suggestion very much. Now, each column has 15 rows of data. Is there a formula to copy the row of information to each column since the dates are on the left column.


----------



## JamesCanale (Dec 14, 2022)

This should do the trick - I made it for four columns of data

MrExcelPlayground14.xlsxABCDEFGHIJKLMNO1DateValueThingElseButWed 12/14/2022211/15/20228688176126DayThu 12/8/2022Fri 12/9/2022Sat 12/10/2022Sun 12/11/2022Mon 12/12/2022Tue 12/13/2022Wed 12/14/2022311/16/2022707214494Value81162011409050411/17/2022111326-24Thing83182213429252511/18/20222325500Else16636442684184104611/19/20227981162112But116-14-6-243413454711/20/20228991182132811/21/2022626412878911/22/20226668136861011/23/202288901801301111/24/20226062124741211/25/2022991012021521311/26/202277791581081411/27/202275771541041511/28/20226163126761611/29/202291931861361711/30/202293951901401812/1/20225759118681912/2/2022141632-182012/3/202276781561062112/4/202279811621122212/5/20227274148982312/6/2022404284342412/7/202290921841342512/8/202281831661162612/9/2022161836-142712/10/2022202244-62812/11/2022111326-242912/12/2022404284343012/13/202290921841343112/14/2022505210454Sheet28Cell FormulasRangeFormulaO1O1=MAX(Table3[Date])C2:C31C2=[@Value]+2D2:D31D2=[@Thing]*2E2:E31E2=[@Else]-50I2:N2I2=J2-1O2O2=TODAY()I3:O6I3=TRANSPOSE(XLOOKUP(I2,Table3[[Date]:[Date]],Table3[[Value]:[But]],"",0))Dynamic array formulas.


----------



## itsgrady (Dec 14, 2022)

JamesCanale said:


> This should do the trick - I made it for four columns of data
> 
> MrExcelPlayground14.xlsxABCDEFGHIJKLMNO1DateValueThingElseButWed 12/14/2022211/15/20228688176126DayThu 12/8/2022Fri 12/9/2022Sat 12/10/2022Sun 12/11/2022Mon 12/12/2022Tue 12/13/2022Wed 12/14/2022311/16/2022707214494Value81162011409050411/17/2022111326-24Thing83182213429252511/18/20222325500Else16636442684184104611/19/20227981162112But116-14-6-243413454711/20/20228991182132811/21/2022626412878911/22/20226668136861011/23/202288901801301111/24/20226062124741211/25/2022991012021521311/26/202277791581081411/27/202275771541041511/28/20226163126761611/29/202291931861361711/30/202293951901401812/1/20225759118681912/2/2022141632-182012/3/202276781561062112/4/202279811621122212/5/20227274148982312/6/2022404284342412/7/202290921841342512/8/202281831661162612/9/2022161836-142712/10/2022202244-62812/11/2022111326-242912/12/2022404284343012/13/202290921841343112/14/2022505210454Sheet28Cell FormulasRangeFormulaO1O1=MAX(Table3[Date])C2:C31C2=[@Value]+2D2:D31D2=[@Thing]*2E2:E31E2=[@Else]-50I2:N2I2=J2-1O2O2=TODAY()I3:O6I3=TRANSPOSE(XLOOKUP(I2,Table3[[Date]:[Date]],Table3[[Value]:[But]],"",0))Dynamic array formulas.



I really appreciate the help on this project... Your way is a lot better because it keeps an history on a table. Thank you so much! Merry Christmas!


----------



## itsgrady (Dec 14, 2022)

JamesCanale said:


> This should do the trick - I made it for four columns of data
> 
> MrExcelPlayground14.xlsxABCDEFGHIJKLMNO1DateValueThingElseButWed 12/14/2022211/15/20228688176126DayThu 12/8/2022Fri 12/9/2022Sat 12/10/2022Sun 12/11/2022Mon 12/12/2022Tue 12/13/2022Wed 12/14/2022311/16/2022707214494Value81162011409050411/17/2022111326-24Thing83182213429252511/18/20222325500Else16636442684184104611/19/20227981162112But116-14-6-243413454711/20/20228991182132811/21/2022626412878911/22/20226668136861011/23/202288901801301111/24/20226062124741211/25/2022991012021521311/26/202277791581081411/27/202275771541041511/28/20226163126761611/29/202291931861361711/30/202293951901401812/1/20225759118681912/2/2022141632-182012/3/202276781561062112/4/202279811621122212/5/20227274148982312/6/2022404284342412/7/202290921841342512/8/202281831661162612/9/2022161836-142712/10/2022202244-62812/11/2022111326-242912/12/2022404284343012/13/202290921841343112/14/2022505210454Sheet28Cell FormulasRangeFormulaO1O1=MAX(Table3[Date])C2:C31C2=[@Value]+2D2:D31D2=[@Thing]*2E2:E31E2=[@Else]-50I2:N2I2=J2-1O2O2=TODAY()I3:O6I3=TRANSPOSE(XLOOKUP(I2,Table3[[Date]:[Date]],Table3[[Value]:[But]],"",0))Dynamic array formulas.



I'm having a difficult setting up my array... I guess the define names are throwing me off some... I can the column data to return to one cell on the table but not sure how to fill the entire column in the table.


----------



## JamesCanale (Dec 15, 2022)

See where I have this:
Table3[[Value]:[But]]
This is actually pulling an array, not just one cell into the table in columns I to O.  It's outputting everything in the matching row from [Value] to [But].  Then I transpose it to make it go vertical.


----------



## itsgrady (Dec 15, 2022)

JamesCanale said:


> See where I have this:
> Table3[[Value]:[But]]
> This is actually pulling an array, not just one cell into the table in columns I to O.  It's outputting everything in the matching row from [Value] to [But].  Then I transpose it to make it go vertical.


----------



## itsgrady (Dec 15, 2022)

itsgrady said:


> View attachment 80987



I had a hard time follow the vlookup formula---excel says that I have to many arguments. I count five... I like your solution but I am struggling or just tried. 

Thanks for your help.


----------



## JamesCanale (Dec 16, 2022)

MrExcelPlayground14.xlsxDEFGHIJKLMNOPQRSTUV12Aspirate InvPounds Incinterated3DateAcidBaseOxidizerReactT1 dayt1 nightt2dayt2night4Tuesday, December 6, 2022104696552102078485AspriateSat 12/10/2022Sun 12/11/2022Mon 12/12/2022Tue 12/13/2022Wed 12/14/2022Thu 12/15/2022Fri 12/16/2022Wednesday, December 7, 2022119721697102468856Acid16804533447288Thursday, December 8, 2022756784295002598497Base92307152935158Friday, December 9, 2022727845423507253538Oxidizer94628664332556Saturday, December 10, 2022169294398009271979React39448835824018Sunday, December 11, 20228030624448026134010Aspirate Tot241216290184252188220Monday, December 12, 20224571868841041725411Tuesday, December 13, 20223352643574092339012TotalSat 12/10/2022Sun 12/11/2022Mon 12/12/2022Tue 12/13/2022Wed 12/14/2022Thu 12/15/2022Fri 12/16/2022Wednesday, December 14, 20224493338296093928513T1 day800480410740960800970Thursday, December 15, 20227251254080053906614t1 night92264192935381Friday, December 16, 20228858561897081169715t2day7113723392901616t2night9740549085669717Tot1060559577955123010091164Sheet31Cell FormulasRangeFormulaE5:J5,E12:J12E5=F5-1K5,K12K5=MAX(Table1[Date])E6:K9E6=TRANSPOSE(XLOOKUP(E5,Table1[[Date]:[Date]],Table1[[Acid]:[React]],"",0))E10:K10,E17:K17E10=SUM(E6#)E13:K16E13=TRANSPOSE(XLOOKUP(E12,Table1[[Date]:[Date]],Table1[[T1 day]:[t2night]],"",0))N5:N14N5=N4+1Dynamic array formulas.


----------



## itsgrady (Dec 13, 2022)

I have a spreadsheet with days of the week as the header for each column. 

What I would like to do is use the last column (7th column) for current information. The seventh column will always be the current date - the other 6 columns will be the previous days.

The next day I would like for all data move over to the left and the oldest day roll off the form. So, the current 7 days will always be on the form. 

Is there an easy formula to move everything over each day. I was thinking to use a button with VBA code, unless there is an easier way. 

Thanks for the help.


----------



## itsgrady (Dec 22, 2022)

JamesCanale said:


> MrExcelPlayground14.xlsxDEFGHIJKLMNOPQRSTUV12Aspirate InvPounds Incinterated3DateAcidBaseOxidizerReactT1 dayt1 nightt2dayt2night4Tuesday, December 6, 2022104696552102078485AspriateSat 12/10/2022Sun 12/11/2022Mon 12/12/2022Tue 12/13/2022Wed 12/14/2022Thu 12/15/2022Fri 12/16/2022Wednesday, December 7, 2022119721697102468856Acid16804533447288Thursday, December 8, 2022756784295002598497Base92307152935158Friday, December 9, 2022727845423507253538Oxidizer94628664332556Saturday, December 10, 2022169294398009271979React39448835824018Sunday, December 11, 20228030624448026134010Aspirate Tot241216290184252188220Monday, December 12, 20224571868841041725411Tuesday, December 13, 20223352643574092339012TotalSat 12/10/2022Sun 12/11/2022Mon 12/12/2022Tue 12/13/2022Wed 12/14/2022Thu 12/15/2022Fri 12/16/2022Wednesday, December 14, 20224493338296093928513T1 day800480410740960800970Thursday, December 15, 20227251254080053906614t1 night92264192935381Friday, December 16, 20228858561897081169715t2day7113723392901616t2night9740549085669717Tot1060559577955123010091164Sheet31Cell FormulasRangeFormulaE5:J5,E12:J12E5=F5-1K5,K12K5=MAX(Table1[Date])E6:K9E6=TRANSPOSE(XLOOKUP(E5,Table1[[Date]:[Date]],Table1[[Acid]:[React]],"",0))E10:K10,E17:K17E10=SUM(E6#)E13:K16E13=TRANSPOSE(XLOOKUP(E12,Table1[[Date]:[Date]],Table1[[T1 day]:[t2night]],"",0))N5:N14N5=N4+1Dynamic array formulas.


Thank you so much for all your help! The project looks great! I'm very, very happy with it.


----------

