DarbyBrown
New Member
- Joined
- Jan 22, 2016
- Messages
- 31
- Office Version
- 365
- Platform
- 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.
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 | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Pennies | Nickles | Dimes | Quarters | Dollars | Date | Total | ||
| 2 | 84 | 25 | 40 | 23 | 0 | 12/30/20 | $11.84 | ||
| 3 | 5 | 3 | 4 | 5 | 01/15/21 | $1.85 | |||
| 4 | 4 | 0 | 1 | 2 | 01/18/21 | $0.64 | |||
| 5 | 2 | 2 | 5 | 3 | 01/23/21 | $1.37 | |||
| 6 | 6 | 0 | 2 | 2 | 11 | 01/28/21 | $11.76 | ||
| 7 | $0.00 | ||||||||
| 8 | $0.00 | ||||||||
| 9 | $0.00 | ||||||||
| 10 | $27.46 | ||||||||
| 11 | Total in coin jar = | (start) | (end) | $27.46 | |||||
Jan21 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G2:G9 | G2 | =SUM([@Pennies]*0.01,[@Nickles]*0.05,[@Dimes]*0.1,[@Quarters]*0.25,[@Dollars]) |
| G10 | G10 | =SUM(G2:G9) |
| G11 | G11 | =SUM(G10,D11) |
| Coin Counter.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Pennies | Nickles | Dimes | Quarters | Dollars | Date | Total | ||
| 2 | 0 | 1 | 1 | 3 | 0 | 02/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 | |||
| 11 | Total in coin jar = | (start) | $27.46 | (end) | $28.36 | ||||
Feb21 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A10 | A10 | =SUM(A2:A9)/100 |
| B10 | B10 | =SUM(B2:B9)*0.05 |
| C10 | C10 | =SUM(C2:C9)*0.1 |
| E10,G10 | E10 | =SUM(E2:E9) |
| D10 | D10 | =SUM(D2:D9)*0.25 |
| D11 | D11 | ='Jan21'!G10 |
| G2:G9 | G2 | =SUM([@Pennies]*0.01,[@Nickles]*0.05,[@Dimes]*0.1,[@Quarters]*0.25,[@Dollars]) |
| G11 | G11 | =SUM(G10,D11) |
| Coin Counter.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Pennies | Nickles | Dimes | Quarters | Dollars | Date | Total | ||
| 2 | 0 | 1 | 1 | 3 | 0 | 02/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 | |||
| 11 | Total in coin jar = | (start) | $27.46 | (end) | $28.36 | ||||
Feb21 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A10 | A10 | =SUM(A2:A9)/100 |
| B10 | B10 | =SUM(B2:B9)*0.05 |
| C10 | C10 | =SUM(C2:C9)*0.1 |
| E10,G10 | E10 | =SUM(E2:E9) |
| D10 | D10 | =SUM(D2:D9)*0.25 |
| D11 | D11 | ='Jan21'!G10 |
| G2:G9 | G2 | =SUM([@Pennies]*0.01,[@Nickles]*0.05,[@Dimes]*0.1,[@Quarters]*0.25,[@Dollars]) |
| G11 | G11 | =SUM(G10,D11) |
