kumara_faith
Well-known Member
- Joined
- Aug 19, 2006
- Messages
- 951
- Office Version
- 365
Hi,
I have the following table in sheet Expense Journal:
I have the following in the Budget sheet:
I am trying to create another Summary table as below:
1) The Budget sheet has the fixed Budget expenses that is forecasted to be incurred during this period.
2) The user will input the actual expenses daily in the Expense Journal sheet
3) In the Summary sheet, the formula should populate all actual expenses from the Expense Journal sheet and populate the balance from the Budget sheet.
4) Example of the correct result is shown as above in the Summary sheet where expenses incurred from Nov 14 2023 to Nov 26 2023 is populated from the Expense Journal and the rest is populated from Budget sheet
Appreciate any help if this can be achieved via a formula. Thanks
I have the following table in sheet Expense Journal:
Expense Tracker_2023__Actual Exopense Record.xlsx | |||||
---|---|---|---|---|---|
B | C | D | |||
8 | Date | Expense | Amount (INR) | ||
9 | Tuesday, November 14, 2023 | Taxi | 3,900 | ||
10 | Wednesday, November 15, 2023 | Parikrama Cost | 550 | ||
11 | Wednesday, November 15, 2023 | Incense | 250 | ||
12 | Wednesday, November 15, 2023 | Biscuit | 30 | ||
13 | Wednesday, November 15, 2023 | Lighter | 20 | ||
14 | Thursday, November 16, 2023 | Parikrama Cost | 500 | ||
15 | Wednesday, November 15, 2023 | Dinner | 400 | ||
16 | Thursday, November 16, 2023 | Rickshaw | 20 | ||
17 | Thursday, November 16, 2023 | Dinner | 220 | ||
18 | Saturday, November 18, 2023 | Parikrama Cost | 1,750 | ||
19 | Friday, November 17, 2023 | Parikrama Cost | 1,800 | ||
20 | Friday, November 17, 2023 | Laundry | 450 | ||
21 | Friday, November 17, 2023 | Donation | 600 | ||
22 | Friday, November 17, 2023 | Rickshaw | 400 | ||
23 | Friday, November 17, 2023 | Incense | 240 | ||
24 | Friday, November 17, 2023 | Rickshaw | 3,900 | ||
25 | Saturday, November 18, 2023 | Rickshaw | 150 | ||
26 | Saturday, November 18, 2023 | Souvenirs | 200 | ||
27 | Saturday, November 18, 2023 | Donation | 500 | ||
28 | Saturday, November 18, 2023 | Donation | 50 | ||
29 | Saturday, November 18, 2023 | Medicine | 1,250 | ||
30 | Saturday, November 18, 2023 | Lunch | 340 | ||
31 | Saturday, November 18, 2023 | Book | 20 | ||
32 | Saturday, November 18, 2023 | Donation | 10 | ||
33 | Saturday, November 18, 2023 | Donation | 30 | ||
34 | Saturday, November 18, 2023 | Dinner | 60 | ||
35 | Saturday, November 18, 2023 | Medicine | 40 | ||
36 | Saturday, November 18, 2023 | Donation | 1,000 | ||
37 | Sunday, November 19, 2023 | Breakfast | 120 | ||
38 | Sunday, November 19, 2023 | Biscuit | 590 | ||
39 | Sunday, November 19, 2023 | Lunch | 170 | ||
40 | Sunday, November 19, 2023 | Donation | 10 | ||
41 | Sunday, November 19, 2023 | Medicine | 36 | ||
42 | Monday, November 20, 2023 | Breakfast | 80 | ||
43 | Monday, November 20, 2023 | Lunch | 170 | ||
44 | Monday, November 20, 2023 | Medicine | 70 | ||
45 | Monday, November 20, 2023 | Dinner | 80 | ||
46 | Monday, November 20, 2023 | Parikrama Cost | 500 | ||
47 | Monday, November 20, 2023 | Laundry | 290 | ||
48 | Tuesday, November 21, 2023 | Book | 500 | ||
49 | Tuesday, November 21, 2023 | Donation | 100 | ||
50 | Tuesday, November 21, 2023 | Dinner | 140 | ||
51 | Tuesday, November 21, 2023 | Medicine | 100 | ||
52 | Wednesday, November 22, 2023 | Breakfast | 80 | ||
53 | Wednesday, November 22, 2023 | Lunch | 200 | ||
54 | Wednesday, November 22, 2023 | Dinner | 200 | ||
55 | Thursday, November 23, 2023 | Lunch | 360 | ||
56 | Thursday, November 23, 2023 | Dinner | 200 | ||
57 | Thursday, November 23, 2023 | Donation | 40 | ||
58 | Thursday, November 23, 2023 | Medicine | 1,250 | ||
59 | Thursday, November 23, 2023 | Parikrama Cost | 500 | ||
60 | Thursday, November 23, 2023 | Lunch | 320 | ||
61 | Thursday, November 23, 2023 | Rickshaw | 3,700 | ||
62 | Friday, November 24, 2023 | Breakfast | 80 | ||
63 | Friday, November 24, 2023 | Lunch | 200 | ||
64 | Friday, November 24, 2023 | Donation | 9,000 | ||
65 | Friday, November 24, 2023 | Medicine | 660 | ||
66 | Saturday, November 25, 2023 | Breakfast | 80 | ||
67 | Saturday, November 25, 2023 | Lunch | 200 | ||
68 | Saturday, November 25, 2023 | Dinner | 200 | ||
69 | Sunday, November 26, 2023 | Breakfast | 80 | ||
70 | Sunday, November 26, 2023 | Lunch | 200 | ||
Expense Journal |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
B9:B170 | List | =Date |
C9:C170 | List | =Expense1 |
I have the following in the Budget sheet:
Expense Tracker_2023__Actual Exopense Record.xlsx | |||||
---|---|---|---|---|---|
B | C | D | |||
7 | Date | Expense | Amount (INR) | ||
8 | Flight | 37,166 | |||
9 | Accommodation | 34,771 | |||
10 | Tuesday, November 14, 2023 | Taxi | 3,500 | ||
11 | Wednesday, November 15, 2023 | Parikrama Cost | 350 | ||
12 | Wednesday, November 15, 2023 | Sim Card | 1,000 | ||
13 | Wednesday, November 15, 2023 | Breakfast | 200 | ||
14 | Wednesday, November 15, 2023 | Lunch | 200 | ||
15 | Wednesday, November 15, 2023 | Dinner | 200 | ||
16 | Wednesday, November 15, 2023 | Donation | 10 | ||
17 | Thursday, November 16, 2023 | Parikrama Cost | 350 | ||
18 | Thursday, November 16, 2023 | Breakfast | 200 | ||
19 | Thursday, November 16, 2023 | Lunch | 200 | ||
20 | Thursday, November 16, 2023 | Dinner | 200 | ||
21 | Thursday, November 16, 2023 | Donation | 10 | ||
22 | Friday, November 17, 2023 | Parikrama Cost | 350 | ||
23 | Friday, November 17, 2023 | Breakfast | 280 | ||
24 | Friday, November 17, 2023 | Lunch | 300 | ||
25 | Friday, November 17, 2023 | Dinner | 300 | ||
26 | Friday, November 17, 2023 | Donation | 10 | ||
27 | Friday, November 17, 2023 | Water | 50 | ||
28 | Saturday, November 18, 2023 | Parikrama Cost | 350 | ||
29 | Saturday, November 18, 2023 | Breakfast | 200 | ||
30 | Saturday, November 18, 2023 | Lunch | 200 | ||
31 | Saturday, November 18, 2023 | Dinner | 200 | ||
32 | Saturday, November 18, 2023 | Donation | 10 | ||
33 | Sunday, November 19, 2023 | Parikrama Cost | 350 | ||
34 | Sunday, November 19, 2023 | Breakfast | 200 | ||
35 | Sunday, November 19, 2023 | Lunch | 200 | ||
36 | Sunday, November 19, 2023 | Dinner | 200 | ||
37 | Sunday, November 19, 2023 | Donation | 10 | ||
38 | Monday, November 20, 2023 | Parikrama Cost | 350 | ||
39 | Monday, November 20, 2023 | Breakfast | 200 | ||
40 | Monday, November 20, 2023 | Lunch | 200 | ||
41 | Monday, November 20, 2023 | Dinner | 200 | ||
42 | Monday, November 20, 2023 | Donation | 10 | ||
43 | Tuesday, November 21, 2023 | Parikrama Cost | 350 | ||
44 | Tuesday, November 21, 2023 | Breakfast | 200 | ||
45 | Tuesday, November 21, 2023 | Lunch | 200 | ||
46 | Tuesday, November 21, 2023 | Dinner | 200 | ||
47 | Tuesday, November 21, 2023 | Donation | 10 | ||
48 | Wednesday, November 22, 2023 | Parikrama Cost | 350 | ||
49 | Wednesday, November 22, 2023 | Breakfast | 200 | ||
50 | Wednesday, November 22, 2023 | Lunch | 200 | ||
51 | Wednesday, November 22, 2023 | Dinner | 200 | ||
52 | Wednesday, November 22, 2023 | Donation | 10 | ||
53 | Thursday, November 23, 2023 | Parikrama Cost | 350 | ||
54 | Thursday, November 23, 2023 | Breakfast | 200 | ||
55 | Thursday, November 23, 2023 | Lunch | 200 | ||
56 | Thursday, November 23, 2023 | Dinner | 200 | ||
57 | Thursday, November 23, 2023 | Donation | 10 | ||
58 | Thursday, November 23, 2023 | Water | 50 | ||
59 | Thursday, November 23, 2023 | Biscuit | 1,200 | ||
60 | Friday, November 24, 2023 | Parikrama Cost | 350 | ||
61 | Friday, November 24, 2023 | Breakfast | 200 | ||
62 | Friday, November 24, 2023 | Lunch | 200 | ||
63 | Friday, November 24, 2023 | Dinner | 200 | ||
64 | Friday, November 24, 2023 | Donation | 10 | ||
65 | Saturday, November 25, 2023 | Parikrama Cost | 350 | ||
66 | Saturday, November 25, 2023 | Breakfast | 200 | ||
67 | Saturday, November 25, 2023 | Lunch | 200 | ||
68 | Saturday, November 25, 2023 | Dinner | 200 | ||
69 | Saturday, November 25, 2023 | Donation | 10 | ||
70 | Sunday, November 26, 2023 | Parikrama Cost | 350 | ||
71 | Sunday, November 26, 2023 | Breakfast | 200 | ||
72 | Sunday, November 26, 2023 | Lunch | 200 | ||
73 | Sunday, November 26, 2023 | Dinner | 200 | ||
74 | Sunday, November 26, 2023 | Donation | 10 | ||
75 | Monday, November 27, 2023 | Incense | 2,000 | ||
76 | Monday, November 27, 2023 | Taxi | 3,500 | ||
77 | Monday, November 27, 2023 | Donation | 10,200 | ||
78 | Monday, November 27, 2023 | Book | 1,000 | ||
Budget |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
B10:B360 | List | =Date |
C10:C474 | List | =Expense1 |
I am trying to create another Summary table as below:
Expense Tracker_2023__Actual Exopense Record.xlsx | |||||
---|---|---|---|---|---|
B | C | D | |||
9 | Date | Expense | Amount (INR) | ||
10 | Tuesday, November 14, 2023 | Taxi | 3900 | ||
11 | Wednesday, November 15, 2023 | Parikrama Cost | 550 | ||
12 | Wednesday, November 15, 2023 | Incense | 250 | ||
13 | Wednesday, November 15, 2023 | Biscuit | 30 | ||
14 | Wednesday, November 15, 2023 | Lighter | 20 | ||
15 | Thursday, November 16, 2023 | Parikrama Cost | 500 | ||
16 | Wednesday, November 15, 2023 | Dinner | 400 | ||
17 | Thursday, November 16, 2023 | Rickshaw | 20 | ||
18 | Thursday, November 16, 2023 | Dinner | 220 | ||
19 | Saturday, November 18, 2023 | Parikrama Cost | 1750 | ||
20 | Friday, November 17, 2023 | Parikrama Cost | 1800 | ||
21 | Friday, November 17, 2023 | Laundry | 450 | ||
22 | Friday, November 17, 2023 | Donation | 600 | ||
23 | Friday, November 17, 2023 | Rickshaw | 400 | ||
24 | Friday, November 17, 2023 | Incense | 240 | ||
25 | Friday, November 17, 2023 | Rickshaw | 3900 | ||
26 | Saturday, November 18, 2023 | Rickshaw | 150 | ||
27 | Saturday, November 18, 2023 | Souvenirs | 200 | ||
28 | Saturday, November 18, 2023 | Donation | 500 | ||
29 | Saturday, November 18, 2023 | Donation | 50 | ||
30 | Saturday, November 18, 2023 | Medicine | 1250 | ||
31 | Saturday, November 18, 2023 | Lunch | 340 | ||
32 | Saturday, November 18, 2023 | Book | 20 | ||
33 | Saturday, November 18, 2023 | Donation | 10 | ||
34 | Saturday, November 18, 2023 | Donation | 30 | ||
35 | Saturday, November 18, 2023 | Dinner | 60 | ||
36 | Saturday, November 18, 2023 | Medicine | 40 | ||
37 | Saturday, November 18, 2023 | Donation | 1000 | ||
38 | Sunday, November 19, 2023 | Breakfast | 120 | ||
39 | Sunday, November 19, 2023 | Biscuit | 590 | ||
40 | Sunday, November 19, 2023 | Lunch | 170 | ||
41 | Sunday, November 19, 2023 | Donation | 10 | ||
42 | Sunday, November 19, 2023 | Medicine | 36 | ||
43 | Monday, November 20, 2023 | Breakfast | 80 | ||
44 | Monday, November 20, 2023 | Lunch | 170 | ||
45 | Monday, November 20, 2023 | Medicine | 70 | ||
46 | Monday, November 20, 2023 | Dinner | 80 | ||
47 | Monday, November 20, 2023 | Parikrama Cost | 500 | ||
48 | Monday, November 20, 2023 | Laundry | 290 | ||
49 | Tuesday, November 21, 2023 | Book | 500 | ||
50 | Tuesday, November 21, 2023 | Donation | 100 | ||
51 | Tuesday, November 21, 2023 | Dinner | 140 | ||
52 | Tuesday, November 21, 2023 | Medicine | 100 | ||
53 | Wednesday, November 22, 2023 | Breakfast | 80 | ||
54 | Wednesday, November 22, 2023 | Lunch | 200 | ||
55 | Wednesday, November 22, 2023 | Dinner | 200 | ||
56 | Thursday, November 23, 2023 | Lunch | 360 | ||
57 | Thursday, November 23, 2023 | Dinner | 200 | ||
58 | Thursday, November 23, 2023 | Donation | 40 | ||
59 | Thursday, November 23, 2023 | Medicine | 1250 | ||
60 | Thursday, November 23, 2023 | Parikrama Cost | 500 | ||
61 | Thursday, November 23, 2023 | Lunch | 320 | ||
62 | Thursday, November 23, 2023 | Rickshaw | 3700 | ||
63 | Friday, November 24, 2023 | Breakfast | 80 | ||
64 | Friday, November 24, 2023 | Lunch | 200 | ||
65 | Friday, November 24, 2023 | Donation | 9000 | ||
66 | Friday, November 24, 2023 | Medicine | 660 | ||
67 | Saturday, November 25, 2023 | Breakfast | 80 | ||
68 | Saturday, November 25, 2023 | Lunch | 200 | ||
69 | Saturday, November 25, 2023 | Dinner | 200 | ||
70 | Sunday, November 26, 2023 | Breakfast | 80 | ||
71 | Sunday, November 26, 2023 | Lunch | 200 | ||
72 | Monday, November 27, 2023 | Incense | 2000 | ||
73 | Monday, November 27, 2023 | Taxi | 3500 | ||
74 | Monday, November 27, 2023 | Donation | 10200 | ||
75 | Monday, November 27, 2023 | Book | 1000 | ||
Actual&Forecast (2) |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B9:D9 | B9 | ='Expense Journal'!B8 |
1) The Budget sheet has the fixed Budget expenses that is forecasted to be incurred during this period.
2) The user will input the actual expenses daily in the Expense Journal sheet
3) In the Summary sheet, the formula should populate all actual expenses from the Expense Journal sheet and populate the balance from the Budget sheet.
4) Example of the correct result is shown as above in the Summary sheet where expenses incurred from Nov 14 2023 to Nov 26 2023 is populated from the Expense Journal and the rest is populated from Budget sheet
Appreciate any help if this can be achieved via a formula. Thanks