# Obtain the value of a cell from the previous worksheet

#### DarbyBrown

##### New Member
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)

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

#### etaf

##### Well-known Member
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
Untested but maybe something like this in D11:
=INDIRECT(TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"mmmyy")&"!G11")

#### DarbyBrown

##### New Member
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

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
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

#### DarbyBrown

##### New Member
You are welcome - thanks for the reply.
Just to let you know, It Worked

#### JoeMo

##### MrExcel MVP
Just to let you know, It Worked
Glad to hear that - thanks for letting me know.

Replies
1
Views
85
Replies
0
Views
268
Replies
1
Views
2K

Threads
1,129,673
Messages
5,637,717
Members
416,981
Latest member
PLonchar

### 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

### 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