MrDB4Excel
Active Member
- Joined
- Jan 29, 2004
- Messages
- 341
- Office Version
- 2013
- Platform
- Windows
Having tried several options it seems I am missing something.
I need a formula that will check column “C” for the text ‘Balances With * Statement’ and then get the month from that row in column “B”.
Obviously, each time the cell contents showing ‘Balances With * Statement’, the Month is displayed per the formula that resides in each case of ‘Balances With * Statement’ and this seems to be the sticky part in that I need each case of ‘Balances With * Statement’ to return the month shown in column “B” per the row of each instance of ‘Balances With * Statement’ and put that month as the month number in column “G” and as the month name in column “I” which in turn provides data from column “G” into the formula in the relevant row number in column “H” giving the total of each month’s expenses. This formula result in column “H” is then applied elsewhere in another workbook.
Obviously, these are all bogus amounts and some are also bogus expense types but given here to create this bogus sheet which will then be a template for the real sheet that this data is loosely based upon.
I need a formula that will check column “C” for the text ‘Balances With * Statement’ and then get the month from that row in column “B”.
Obviously, each time the cell contents showing ‘Balances With * Statement’, the Month is displayed per the formula that resides in each case of ‘Balances With * Statement’ and this seems to be the sticky part in that I need each case of ‘Balances With * Statement’ to return the month shown in column “B” per the row of each instance of ‘Balances With * Statement’ and put that month as the month number in column “G” and as the month name in column “I” which in turn provides data from column “G” into the formula in the relevant row number in column “H” giving the total of each month’s expenses. This formula result in column “H” is then applied elsewhere in another workbook.
Obviously, these are all bogus amounts and some are also bogus expense types but given here to create this bogus sheet which will then be a template for the real sheet that this data is loosely based upon.
| PercentCheck.xlsx | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | NUMBER OR CODE | Date dd/mm/yyyyy | TRANSACTION DESCRIPTION | PAYMENT AMOUNT | DEPOSIT AMOUNT or returns | BALANCE | |||||
| 2 | Currency Conversion Rates Excel | ||||||||||
| 3 | Beginning Balance from end of 2020>> | $10,000.00 | |||||||||
| 4 | $10,000.00 | 1 | $1,600.00 | January | |||||||
| 5 | rd | Jan/06/2021 | Cash Deposit | $1,000.00 | $11,000.00 | 2 | $11,000.00 | February | |||
| 6 | ebp | Jan/08/2021 | January 2021 Health Insurance | $100.00 | $10,900.00 | 3 | $1,900.00 | March | |||
| 7 | ebp | Jan/08/2021 | January 2021 Health Insurance | $100.00 | $10,800.00 | 4 | $600.00 | April | |||
| 8 | ebp | Jan/12/2021 | 12-Mobile-Dec | $100.00 | $10,700.00 | ||||||
| 9 | ebp | Jan/14/2021 | Cellar Storage for January 2021 | $100.00 | $10,600.00 | ||||||
| 10 | ebp | Jan/14/2021 | Electricity for January 2021 | $100.00 | $10,500.00 | ||||||
| 11 | ebp | Jan/14/2021 | Utilities for January 2021 | $100.00 | $10,400.00 | ||||||
| 12 | ebp | Jan/14/2021 | 2021 Internet-Billing | $1,000.00 | $9,400.00 | ||||||
| 13 | bws | Feb/02/2021 | Balances With January Statement | $9,400.00 | |||||||
| 14 | olp-dct | Feb/01/2021 | Amazon.com | $100.00 | $9,300.00 | ||||||
| 15 | olp-dct | Feb/02/2021 | MONEYGRAM | $100.00 | $9,200.00 | ||||||
| 16 | rd | Feb/08/2021 | Cash Deposit | $1,000.00 | $10,200.00 | ||||||
| 17 | ebp | Feb/08/2021 | February 2021 Health Insurance | $100.00 | $10,100.00 | ||||||
| 18 | ebp | Feb/08/2021 | February 2021 Health Insurance | $100.00 | $10,000.00 | ||||||
| 19 | olp-dct | Feb/08/2021 | Amazon.com | $100.00 | $9,900.00 | ||||||
| 20 | ebp | Feb/10/2021 | 01-Mobile-Jan | $100.00 | $9,800.00 | ||||||
| 21 | ebp | Feb/13/2021 | Cellar Storage for February 2021 | $100.00 | $9,700.00 | ||||||
| 22 | ebp | Feb/13/2021 | Electricity for February 2021 | $100.00 | $9,600.00 | ||||||
| 23 | ebp | Feb/13/2021 | Utilities for February 2021 | $100.00 | $9,500.00 | ||||||
| 24 | etr | Feb/18/2021 | MONEYGRAM Refund | $100.00 | $9,600.00 | ||||||
| 25 | etr | Feb/24/2021 | Amazon.com | $100.00 | $9,500.00 | ||||||
| 26 | bws | Mar/02/2021 | Balances With February Statement | $9,500.00 | |||||||
| 27 | cw | Feb/26/2021 | Cash Withdrawal | $10,000.00 | $500.00 | ||||||
| 28 | cw | Mar/01/2021 | Payment card fee for Cash Withdrawal | $100.00 | $600.00 | ||||||
| 29 | cw | Mar/07/2021 | Amazon.com | $100.00 | $700.00 | ||||||
| 30 | ebp | Mar/08/2021 | March 2021 Health Insurance | $100.00 | $800.00 | ||||||
| 31 | ebp | Mar/08/2021 | March 2021 Health Insurance | $100.00 | $900.00 | ||||||
| 32 | rd | Mar/08/2021 | Cash Deposit | $10,000.00 | $9,100.00 | ||||||
| 33 | cw | Mar/08/2021 | United Tickets | $1,000.00 | $8,100.00 | ||||||
| 34 | ebp | Mar/12/2021 | 02-Mobile-Feb | $100.00 | $8,000.00 | ||||||
| 35 | ebp | Mar/12/2021 | Cellar Storage for March 2021 | $100.00 | $7,900.00 | ||||||
| 36 | ebp | Mar/12/2021 | Electricity for March 2021 | $100.00 | $7,800.00 | ||||||
| 37 | ebp | Mar/12/2021 | Utilities for March 2021 | $100.00 | $7,700.00 | ||||||
| 38 | cw | Mar/16/2021 | Amazon.com | $100.00 | $7,600.00 | ||||||
| 39 | etr | Mar/22/2021 | Refund from Health Insurance | $1,000.00 | $8,600.00 | ||||||
| 40 | etr | Mar/29/2021 | Refund from United | $1,000.00 | $9,600.00 | ||||||
| 41 | bws | Apr/02/2021 | Balances With March Statement | $9,600.00 | |||||||
| 42 | ebp | Apr/08/2021 | April 2021 Health Insurance | $100.00 | $9,500.00 | ||||||
| 43 | ebp | Apr/08/2021 | April 2021 Health Insurance | $100.00 | $9,400.00 | ||||||
| 44 | rd | Apr/08/2021 | Cash Deposit | $1,000.00 | $10,400.00 | ||||||
| 45 | ebp | Apr/08/2021 | 03-Mobile-Mar | $100.00 | $10,300.00 | ||||||
| 46 | ebp | Apr/14/2021 | Cellar Storage for April 2021 | $100.00 | $10,200.00 | ||||||
| 47 | ebp | Apr/14/2021 | Electricity for April 2021 | $100.00 | $10,100.00 | ||||||
| 48 | ebp | Apr/14/2021 | Utilities for April 2021 | $100.00 | $10,000.00 | ||||||
| 49 | bws | May/02/2021 | Balances With April Statement | $10,000.00 | |||||||
| 50 | ebp | May/08/2021 | May 2021 Health Insurance | $100.00 | $9,900.00 | ||||||
| 51 | ebp | May/08/2021 | May 2021 Health Insurance | $100.00 | $9,800.00 | ||||||
| 52 | rd | May/06/2021 | Cash Deposit | $1,000.00 | $10,800.00 | ||||||
| 53 | ebp | May/10/2021 | 03-Mobile-Apr | $100.00 | $10,700.00 | ||||||
| 54 | ebp | May/14/2021 | Cellar Storage for May 2021 | $100.00 | $10,600.00 | ||||||
| 55 | ebp | May/14/2021 | Electricity for May 2021 | $100.00 | $10,500.00 | ||||||
| 56 | ebp | May/14/2021 | Utilities for May 2021 | $100.00 | $10,400.00 | ||||||
2021 | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G4 | G4 | =MONTH(B5) |
| H4:H7 | H4 | =SUMPRODUCT((MONTH($B$5:$B$58)=G4)*($D$5:$D$58)) |
| I4 | I4 | =TEXT($B5,"mmmm") |
| G5 | G5 | =MONTH(B13) |
| I5 | I5 | =TEXT($B13,"mmmm") |
| G6 | G6 | =MONTH(B26) |
| I6 | I6 | =TEXT($B26,"mmmm") |
| G7 | G7 | =MONTH(B41) |
| I7 | I7 | =TEXT($B41,"mmmm") |
| C50:C51,C42:C43,C30:C31,C17:C18,C6:C7 | C6 | =TEXT(B6,"MMMM")&" 2021"&" Health Insurance" |
| C9,C54,C46,C35,C21 | C9 | ="Cellar Storage for "&TEXT(B9,"MMMM")&" 2021" |
| C10,C55,C47,C36,C22 | C10 | ="Electricity for "&TEXT(B10,"MMMM")&" 2021" |
| C11,C56,C48,C37,C23 | C11 | ="Utilities for "&TEXT(B11,"MMMM")&" 2021" |
| C13,C49,C41,C26 | C13 | ="Balances With "&TEXT(B12,"MMMM")&" Statement" |
| F52,F49,F44,F39:F41,F32,F24,F16,F4:F5 | F4 | =SUM(F3+E4) |
| F53:F56,F50:F51,F45:F48,F42:F43,F33:F38,F25:F31,F17:F23,F6:F15 | F6 | =SUM(F5-D6) |
