MrDB4Excel
Active Member
- Joined
- Jan 29, 2004
- Messages
- 348
- Office Version
- 2013
- Platform
- Windows
Based upon the notation "rd" in column "A" and the associated date in column "B" I want to get the value in column "G" and have it populate the corresponding cell on sheet "2020 BudgetSheet" in column "E".
The corresponding formula in the relevant cell in column "E" of the "2020 BudgetSheet" is where the vlookup formula resides, thereby retrieving the value in column "G" in the corresponding dated cell per the relevant month on sheet "Example1".
Is this doable without VBA?
Obviously these payment amounts, deposit amounts, debit amounts, income amounts, transaction descriptions, and bank balances are all bogus, entered here as such for explanation purposes only. Thus no private information is contained in this workbook.
Range capture from "Example1" sheet
Range capture from "2020 BudgetSheet" sheet
The corresponding formula in the relevant cell in column "E" of the "2020 BudgetSheet" is where the vlookup formula resides, thereby retrieving the value in column "G" in the corresponding dated cell per the relevant month on sheet "Example1".
Is this doable without VBA?
Obviously these payment amounts, deposit amounts, debit amounts, income amounts, transaction descriptions, and bank balances are all bogus, entered here as such for explanation purposes only. Thus no private information is contained in this workbook.
Range capture from "Example1" sheet
Return Value In Another Cell.xlsx | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | NUMBER OR CODE | Date | TRANSACTION DESCRIPTION | PAYMENT AMOUNT | ü | ü | DEPOSIT AMOUNT or returns | BALANCE | ||||
2 | Debit | Credit | ||||||||||
3 | $10,000.00 | Based upon the notation "rd" in column "A" and the associated date in column "B" I want to get the value in column "G" and have it populate the corresponding cell on sheet "2020 BudgetSheet" in column "E". The corresponding formula in the relevant cell in column "E" of the "2020 BudgetSheet" is where the vlookup formula resides, thereby retrieving the value in column "G" in the corresponding dated cell per the relevant month on sheet "Example1". Is this doable without VBA? Obviously these payment amounts, deposit amounts, debit amounts, income amounts, transaction descriptions, and bank balances are all bogus, entered here as such for explanation purposes only. Thus no private information is contained in this workbook. | ||||||||||
4 | rd | Jan/06/2020 | $3,000.00 | $13,000.00 | ||||||||
5 | ebp | Jan/11/2020 | $500.00 | $12,500.00 | ||||||||
6 | ebp | Jan/14/2020 | $500.00 | $12,000.00 | ||||||||
7 | ebp | Jan/14/2020 | $500.00 | $11,500.00 | ||||||||
8 | ebp | Jan/14/2020 | $500.00 | $11,000.00 | ||||||||
9 | $11,000.00 | |||||||||||
10 | rd | Feb/05/2020 | $3,000.00 | $14,000.00 | ||||||||
11 | ebp | Feb/10/2020 | $500.00 | $13,500.00 | ||||||||
12 | ebp | Feb/14/2020 | $500.00 | $13,000.00 | ||||||||
13 | ebp | Feb/14/2020 | $500.00 | $12,500.00 | ||||||||
14 | $12,500.00 | |||||||||||
15 | rd | Mar/05/2020 | $3,000.00 | $15,500.00 | ||||||||
16 | ebp | Mar/10/2020 | $500.00 | $15,000.00 | ||||||||
17 | ebp | Mar/13/2020 | $500.00 | $14,500.00 | ||||||||
18 | ebp | Mar/13/2020 | $500.00 | $14,000.00 | ||||||||
Example1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
H4,H15,H10 | H4 | =SUM(H3+G4) |
H16:H18,H11:H14,H5:H9 | H5 | =SUM(H4-D5) |
Range capture from "2020 BudgetSheet" sheet
Return Value In Another Cell.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Month | Date | Description | Category | Income | Debits | Running Balance | ||
2 | 1 | Jan/01/2020 | Bank Balance @ Beginning of January 2020 | Bank Balance | $10,000.00 | ||||
3 | 1 | Jan/01/2020 | Income For January 2020 | Income | $3,000.00 | $13,000.00 | |||
4 | 1 | Jan/01/2020 | Building Utility | Building Utility | $100.00 | $12,900.00 | |||
5 | 1 | Jan/01/2020 | Electric | Electric | $100.00 | $12,800.00 | |||
6 | 1 | Jan/01/2020 | Cell Phone Service #1 | Mobile Phone | $100.00 | $12,700.00 | |||
7 | 1 | Jan/01/2020 | Cell Phone Service #2 | Mobile Phone | $100.00 | $12,600.00 | |||
8 | 1 | Jan/01/2020 | Product | Product | $100.00 | $12,500.00 | |||
9 | 1 | Jan/01/2020 | Restaurant | Restaurant | $100.00 | $12,400.00 | |||
10 | 1 | Jan/01/2020 | Internet | Internet | $100.00 | $12,300.00 | |||
11 | 1 | Jan/01/2020 | Property Taxes | Property Taxes | $100.00 | $12,200.00 | |||
12 | 1 | Jan/01/2020 | Basement Storage | Basement Storage | $100.00 | $12,100.00 | |||
13 | 1 | Jan/01/2020 | Miscellaneous | Miscellaneous | $100.00 | $12,000.00 | |||
14 | 1 | Jan/01/2020 | Medical/ Health Care | Medical | $100.00 | $11,900.00 | |||
15 | 1 | Jan/01/2020 | Bank Account Charges | Misc Expenses | $100.00 | $11,800.00 | |||
16 | 1 | Jan/01/2020 | Train Fare | Misc Expenses | $100.00 | $11,700.00 | |||
17 | 1 | Jan/01/2020 | Bus Fare | Misc Expenses | $100.00 | $11,600.00 | |||
18 | 2 | Feb/01/2020 | Bank Balance @ Beginning of February 2020 | Bank Balance | $11,600.00 | ||||
19 | 2 | Feb/01/2020 | Income For February 2020 | Income | $3,000.00 | $14,600.00 | |||
20 | 2 | Feb/01/2020 | Building Utility | Building Utility | $100.00 | $14,500.00 | |||
21 | 2 | Feb/01/2020 | Electric | Electric | $100.00 | $14,400.00 | |||
22 | 2 | Feb/01/2020 | Cell Phone Service #1 | Mobile Phone | $100.00 | $14,300.00 | |||
23 | 2 | Feb/01/2020 | Cell Phone Service #2 | Mobile Phone | $100.00 | $14,200.00 | |||
24 | 2 | Feb/01/2020 | Product | Product | $100.00 | $14,100.00 | |||
25 | 2 | Feb/01/2020 | Restaurant | Restaurant | $100.00 | $14,000.00 | |||
26 | 2 | Feb/01/2020 | Internet | Internet | $100.00 | $13,900.00 | |||
27 | 2 | Feb/01/2020 | Property Taxes | Property Taxes | $100.00 | $13,800.00 | |||
28 | 2 | Feb/01/2020 | Basement Storage | Basement Storage | $100.00 | $13,700.00 | |||
29 | 2 | Feb/01/2020 | Miscellaneous | Miscellaneous | $100.00 | $13,600.00 | |||
30 | 2 | Feb/01/2020 | Medical/ Health Care | Medical | $100.00 | $13,500.00 | |||
31 | 2 | Feb/01/2020 | Bank Account Charges | Misc Expenses | $100.00 | $13,400.00 | |||
32 | 2 | Feb/01/2020 | Train Fare | Misc Expenses | $100.00 | $13,300.00 | |||
33 | 2 | Feb/01/2020 | Bus Fare | Misc Expenses | $100.00 | $13,200.00 | |||
34 | 3 | Mar/01/2020 | Bank Balance @ Beginning of March 2020 | Bank Balance | $13,200.00 | ||||
35 | 3 | Mar/01/2020 | Income For March 2020 | Income | $3,000.00 | $16,200.00 | |||
36 | 3 | Mar/01/2020 | Building Utility | Building Utility | $100.00 | $16,100.00 | |||
37 | 3 | Mar/01/2020 | Electric | Electric | $100.00 | $16,000.00 | |||
38 | 3 | Mar/01/2020 | Cell Phone Service #1 | Mobile Phone | $100.00 | $15,900.00 | |||
39 | 3 | Mar/01/2020 | Cell Phone Service #2 | Mobile Phone | $100.00 | $15,800.00 | |||
40 | 3 | Mar/01/2020 | Product | Product | $100.00 | $15,700.00 | |||
41 | 3 | Mar/01/2020 | Restaurant | Restaurant | $100.00 | $15,600.00 | |||
42 | 3 | Mar/01/2020 | Internet | Internet | $100.00 | $15,500.00 | |||
43 | 3 | Mar/01/2020 | Property Taxes | Property Taxes | $100.00 | $15,400.00 | |||
44 | 3 | Mar/01/2020 | Basement Storage | Basement Storage | $100.00 | $15,300.00 | |||
45 | 3 | Mar/01/2020 | Miscellaneous | Miscellaneous | $100.00 | $15,200.00 | |||
46 | 3 | Mar/01/2020 | Medical/ Health Care | Medical | $100.00 | $15,100.00 | |||
47 | 3 | Mar/01/2020 | Bank Account Charges | Misc Expenses | $100.00 | $15,000.00 | |||
48 | 3 | Mar/01/2020 | Train Fare | Misc Expenses | $100.00 | $14,900.00 | |||
49 | 3 | Mar/01/2020 | Bus Fare | Misc Expenses | $100.00 | $14,800.00 | |||
50 | 4 | Apr/01/2020 | Bank Balance @ Beginning of April 2020 | Bank Balance | $14,800.00 | ||||
51 | 4 | Apr/01/2020 | Income For April 2020 | Income | $3,000.00 | $17,800.00 | |||
2020 BudgetSheet |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C2,C50,C34,C18 | C2 | ="Bank Balance @ Beginning of "&TEXT(B2,"MMMM")&TEXT(B2," YYYY") |
C3,C51,C35,C19 | C3 | ="Income For "&TEXT(B3,"MMMM")&TEXT(B3," YYYY") |
G3,G51,G35,G19 | G3 | =SUM(E2,E3) |
G36:G49,G20:G33,G4:G17 | G4 | =SUM(G3-F4) |
E18,E50,E34 | E18 | =G17 |
A2:A51 | A2 | =MONTH('2020 BudgetSheet'!$B2) |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
D1:D51 | List | =ValidationList |