Obtain the value of a cell from the previous worksheet

DarbyBrown

New Member
Joined
Jan 22, 2016
Messages
30
Office Version
  1. 365
Platform
  1. Windows
I have a small workbook I use to count the coins I put into my change jar. As I put the change in, I count the pennies, nickels, dimes, quarters, and dollar bills(if any). I keep a running total by coin, by day and by month.

There is a cell where I want to show the total value of the previous month so I can add it to the total of the current month. The previous total for the second month is easy, I just use the following:

=Jan21!G11

But when I get the Mar21 I need the number from :

=Feb21!G11

And so on down the line.

What I do now is, I have a template [Table 3] that I copy for each new month and would like to include a formula in D11 like this:

=[previous page]!G11

so I don't have to enter a new formula every month. How do I tell excel I want the number from ‘the previous sheet’ regardless what the previous sheets label is.

Coin Counter.xlsx
ABCDEFG
1PenniesNicklesDimesQuartersDollarsDateTotal
284254023012/30/20$11.84
3534501/15/21$1.85
4401201/18/21$0.64
5225301/23/21$1.37
660221101/28/21$11.76
7$0.00
8$0.00
9$0.00
10$27.46
11Total in coin jar =(start) (end)$27.46
Jan21
Cell Formulas
RangeFormula
G2:G9G2=SUM([@Pennies]*0.01,[@Nickles]*0.05,[@Dimes]*0.1,[@Quarters]*0.25,[@Dollars])
G10G10=SUM(G2:G9)
G11G11=SUM(G10,D11)


Coin Counter.xlsx
ABCDEFG
1PenniesNicklesDimesQuartersDollarsDateTotal
20113002/02/21$0.90
3$0.00
4$0.00
5$0.00
6$0.00
7$0.00
8$0.00
9$0.00
10$ -$ 0.05$ 0.10$ 0.75$0.00 $0.90
11Total in coin jar =(start)$27.46(end)$28.36
Feb21
Cell Formulas
RangeFormula
A10A10=SUM(A2:A9)/100
B10B10=SUM(B2:B9)*0.05
C10C10=SUM(C2:C9)*0.1
E10,G10E10=SUM(E2:E9)
D10D10=SUM(D2:D9)*0.25
D11D11='Jan21'!G10
G2:G9G2=SUM([@Pennies]*0.01,[@Nickles]*0.05,[@Dimes]*0.1,[@Quarters]*0.25,[@Dollars])
G11G11=SUM(G10,D11)


Coin Counter.xlsx
ABCDEFG
1PenniesNicklesDimesQuartersDollarsDateTotal
20113002/02/21$0.90
3$0.00
4$0.00
5$0.00
6$0.00
7$0.00
8$0.00
9$0.00
10$ -$ 0.05$ 0.10$ 0.75$0.00 $0.90
11Total in coin jar =(start)$27.46(end)$28.36
Feb21
Cell Formulas
RangeFormula
A10A10=SUM(A2:A9)/100
B10B10=SUM(B2:B9)*0.05
C10C10=SUM(C2:C9)*0.1
E10,G10E10=SUM(E2:E9)
D10D10=SUM(D2:D9)*0.25
D11D11='Jan21'!G10
G2:G9G2=SUM([@Pennies]*0.01,[@Nickles]*0.05,[@Dimes]*0.1,[@Quarters]*0.25,[@Dollars])
G11G11=SUM(G10,D11)
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,591
Office Version
  1. 365
Platform
  1. MacOS
this should get the current name of the TAB - change it into a date , then subtract goback a month, so will work when year changes
and reformat into a TEXT MMM YY
=TEXT(DATE(YEAR(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))),MONTH(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)))-1,1),"MMMYY")

then you may have to use a indirect, to use as a Sheet reference and add the cell value

=INDIRECT(TEXT(DATE(YEAR(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))),MONTH(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)))-1,1),"MMMYY")&"!G11")

So should do - [Previous month]!CELL

This assumes you rename the sheet you are on, and then the formula will work with any previous month sheet in the workbook
 
Last edited:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,414
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Untested but maybe something like this in D11:
=INDIRECT(TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"mmmyy")&"!G11")
 
Solution

DarbyBrown

New Member
Joined
Jan 22, 2016
Messages
30
Office Version
  1. 365
Platform
  1. Windows
Untested but maybe something like this in D11:
=INDIRECT(TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"mmmyy")&"!G11")
JoeMo, Looks like it will work. Will know better when March Rolls around. Thanks!!!!
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,414
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

JoeMo, Looks like it will work. Will know better when March Rolls around. Thanks!!!!
You are welcome - thanks for the reply.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,591
Office Version
  1. 365
Platform
  1. MacOS
i thought you created the TAB each month , SO if it was JAN21 , then you created a FEB21 , and you wanted the cells in JAN21 to be referenced , regardless of today's date. using today() would also change previous TAB references and change on the 1st of each month - Hence why i chose to use the TAB name itself, so the reference remains based on the TAB name and would not change each month with the date
 

Watch MrExcel Video

Forum statistics

Threads
1,127,898
Messages
5,627,530
Members
416,250
Latest member
darius_rebelo

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
Top