Populate Based on Actual and Budget

K

kumara_faith

Well-known Member
Joined
Aug 19, 2006
Messages
955
Office Version
  1. 365
Hi,

I have the following table in sheet Expense Journal:

Expense Tracker_2023__Actual Exopense Record.xlsx
BCD
8DateExpenseAmount (INR)
9Tuesday, November 14, 2023Taxi3,900
10Wednesday, November 15, 2023Parikrama Cost550
11Wednesday, November 15, 2023Incense250
12Wednesday, November 15, 2023Biscuit30
13Wednesday, November 15, 2023Lighter20
14Thursday, November 16, 2023Parikrama Cost500
15Wednesday, November 15, 2023Dinner400
16Thursday, November 16, 2023Rickshaw20
17Thursday, November 16, 2023Dinner220
18Saturday, November 18, 2023Parikrama Cost1,750
19Friday, November 17, 2023Parikrama Cost1,800
20Friday, November 17, 2023Laundry450
21Friday, November 17, 2023Donation600
22Friday, November 17, 2023Rickshaw400
23Friday, November 17, 2023Incense240
24Friday, November 17, 2023Rickshaw3,900
25Saturday, November 18, 2023Rickshaw150
26Saturday, November 18, 2023Souvenirs200
27Saturday, November 18, 2023Donation500
28Saturday, November 18, 2023Donation50
29Saturday, November 18, 2023Medicine1,250
30Saturday, November 18, 2023Lunch340
31Saturday, November 18, 2023Book20
32Saturday, November 18, 2023Donation10
33Saturday, November 18, 2023Donation30
34Saturday, November 18, 2023Dinner60
35Saturday, November 18, 2023Medicine40
36Saturday, November 18, 2023Donation1,000
37Sunday, November 19, 2023Breakfast120
38Sunday, November 19, 2023Biscuit590
39Sunday, November 19, 2023Lunch170
40Sunday, November 19, 2023Donation10
41Sunday, November 19, 2023Medicine36
42Monday, November 20, 2023Breakfast80
43Monday, November 20, 2023Lunch170
44Monday, November 20, 2023Medicine70
45Monday, November 20, 2023Dinner80
46Monday, November 20, 2023Parikrama Cost500
47Monday, November 20, 2023Laundry290
48Tuesday, November 21, 2023Book500
49Tuesday, November 21, 2023Donation100
50Tuesday, November 21, 2023Dinner140
51Tuesday, November 21, 2023Medicine100
52Wednesday, November 22, 2023Breakfast80
53Wednesday, November 22, 2023Lunch200
54Wednesday, November 22, 2023Dinner200
55Thursday, November 23, 2023Lunch360
56Thursday, November 23, 2023Dinner200
57Thursday, November 23, 2023Donation40
58Thursday, November 23, 2023Medicine1,250
59Thursday, November 23, 2023Parikrama Cost500
60Thursday, November 23, 2023Lunch320
61Thursday, November 23, 2023Rickshaw3,700
62Friday, November 24, 2023Breakfast80
63Friday, November 24, 2023Lunch200
64Friday, November 24, 2023Donation9,000
65Friday, November 24, 2023Medicine660
66Saturday, November 25, 2023Breakfast80
67Saturday, November 25, 2023Lunch200
68Saturday, November 25, 2023Dinner200
69Sunday, November 26, 2023Breakfast80
70Sunday, November 26, 2023Lunch200
Expense Journal
Cells with Data Validation
CellAllowCriteria
B9:B170List=Date
C9:C170List=Expense1


I have the following in the Budget sheet:

Expense Tracker_2023__Actual Exopense Record.xlsx
BCD
7DateExpenseAmount (INR)
8Flight37,166
9Accommodation34,771
10Tuesday, November 14, 2023Taxi3,500
11Wednesday, November 15, 2023Parikrama Cost350
12Wednesday, November 15, 2023Sim Card1,000
13Wednesday, November 15, 2023Breakfast200
14Wednesday, November 15, 2023Lunch200
15Wednesday, November 15, 2023Dinner200
16Wednesday, November 15, 2023Donation10
17Thursday, November 16, 2023Parikrama Cost350
18Thursday, November 16, 2023Breakfast200
19Thursday, November 16, 2023Lunch200
20Thursday, November 16, 2023Dinner200
21Thursday, November 16, 2023Donation10
22Friday, November 17, 2023Parikrama Cost350
23Friday, November 17, 2023Breakfast280
24Friday, November 17, 2023Lunch300
25Friday, November 17, 2023Dinner300
26Friday, November 17, 2023Donation10
27Friday, November 17, 2023Water50
28Saturday, November 18, 2023Parikrama Cost350
29Saturday, November 18, 2023Breakfast200
30Saturday, November 18, 2023Lunch200
31Saturday, November 18, 2023Dinner200
32Saturday, November 18, 2023Donation10
33Sunday, November 19, 2023Parikrama Cost350
34Sunday, November 19, 2023Breakfast200
35Sunday, November 19, 2023Lunch200
36Sunday, November 19, 2023Dinner200
37Sunday, November 19, 2023Donation10
38Monday, November 20, 2023Parikrama Cost350
39Monday, November 20, 2023Breakfast200
40Monday, November 20, 2023Lunch200
41Monday, November 20, 2023Dinner200
42Monday, November 20, 2023Donation10
43Tuesday, November 21, 2023Parikrama Cost350
44Tuesday, November 21, 2023Breakfast200
45Tuesday, November 21, 2023Lunch200
46Tuesday, November 21, 2023Dinner200
47Tuesday, November 21, 2023Donation10
48Wednesday, November 22, 2023Parikrama Cost350
49Wednesday, November 22, 2023Breakfast200
50Wednesday, November 22, 2023Lunch200
51Wednesday, November 22, 2023Dinner200
52Wednesday, November 22, 2023Donation10
53Thursday, November 23, 2023Parikrama Cost350
54Thursday, November 23, 2023Breakfast200
55Thursday, November 23, 2023Lunch200
56Thursday, November 23, 2023Dinner200
57Thursday, November 23, 2023Donation10
58Thursday, November 23, 2023Water50
59Thursday, November 23, 2023Biscuit1,200
60Friday, November 24, 2023Parikrama Cost350
61Friday, November 24, 2023Breakfast200
62Friday, November 24, 2023Lunch200
63Friday, November 24, 2023Dinner200
64Friday, November 24, 2023Donation10
65Saturday, November 25, 2023Parikrama Cost350
66Saturday, November 25, 2023Breakfast200
67Saturday, November 25, 2023Lunch200
68Saturday, November 25, 2023Dinner200
69Saturday, November 25, 2023Donation10
70Sunday, November 26, 2023Parikrama Cost350
71Sunday, November 26, 2023Breakfast200
72Sunday, November 26, 2023Lunch200
73Sunday, November 26, 2023Dinner200
74Sunday, November 26, 2023Donation10
75Monday, November 27, 2023Incense2,000
76Monday, November 27, 2023Taxi3,500
77Monday, November 27, 2023Donation10,200
78Monday, November 27, 2023Book1,000
Budget
Cells with Data Validation
CellAllowCriteria
B10:B360List=Date
C10:C474List=Expense1


I am trying to create another Summary table as below:

Expense Tracker_2023__Actual Exopense Record.xlsx
BCD
9DateExpenseAmount (INR)
10Tuesday, November 14, 2023Taxi3900
11Wednesday, November 15, 2023Parikrama Cost550
12Wednesday, November 15, 2023Incense250
13Wednesday, November 15, 2023Biscuit30
14Wednesday, November 15, 2023Lighter20
15Thursday, November 16, 2023Parikrama Cost500
16Wednesday, November 15, 2023Dinner400
17Thursday, November 16, 2023Rickshaw20
18Thursday, November 16, 2023Dinner220
19Saturday, November 18, 2023Parikrama Cost1750
20Friday, November 17, 2023Parikrama Cost1800
21Friday, November 17, 2023Laundry450
22Friday, November 17, 2023Donation600
23Friday, November 17, 2023Rickshaw400
24Friday, November 17, 2023Incense240
25Friday, November 17, 2023Rickshaw3900
26Saturday, November 18, 2023Rickshaw150
27Saturday, November 18, 2023Souvenirs200
28Saturday, November 18, 2023Donation500
29Saturday, November 18, 2023Donation50
30Saturday, November 18, 2023Medicine1250
31Saturday, November 18, 2023Lunch340
32Saturday, November 18, 2023Book20
33Saturday, November 18, 2023Donation10
34Saturday, November 18, 2023Donation30
35Saturday, November 18, 2023Dinner60
36Saturday, November 18, 2023Medicine40
37Saturday, November 18, 2023Donation1000
38Sunday, November 19, 2023Breakfast120
39Sunday, November 19, 2023Biscuit590
40Sunday, November 19, 2023Lunch170
41Sunday, November 19, 2023Donation10
42Sunday, November 19, 2023Medicine36
43Monday, November 20, 2023Breakfast80
44Monday, November 20, 2023Lunch170
45Monday, November 20, 2023Medicine70
46Monday, November 20, 2023Dinner80
47Monday, November 20, 2023Parikrama Cost500
48Monday, November 20, 2023Laundry290
49Tuesday, November 21, 2023Book500
50Tuesday, November 21, 2023Donation100
51Tuesday, November 21, 2023Dinner140
52Tuesday, November 21, 2023Medicine100
53Wednesday, November 22, 2023Breakfast80
54Wednesday, November 22, 2023Lunch200
55Wednesday, November 22, 2023Dinner200
56Thursday, November 23, 2023Lunch360
57Thursday, November 23, 2023Dinner200
58Thursday, November 23, 2023Donation40
59Thursday, November 23, 2023Medicine1250
60Thursday, November 23, 2023Parikrama Cost500
61Thursday, November 23, 2023Lunch320
62Thursday, November 23, 2023Rickshaw3700
63Friday, November 24, 2023Breakfast80
64Friday, November 24, 2023Lunch200
65Friday, November 24, 2023Donation9000
66Friday, November 24, 2023Medicine660
67Saturday, November 25, 2023Breakfast80
68Saturday, November 25, 2023Lunch200
69Saturday, November 25, 2023Dinner200
70Sunday, November 26, 2023Breakfast80
71Sunday, November 26, 2023Lunch200
72Monday, November 27, 2023Incense2000
73Monday, November 27, 2023Taxi3500
74Monday, November 27, 2023Donation10200
75Monday, November 27, 2023Book1000
Actual&Forecast (2)
Cell Formulas
RangeFormula
B9:D9B9='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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
This works if the Date columns contain actual dates.

Excel Formula: 
=VSTACK( tbl_Expenses, FILTER( tbl_Budget, tbl_Budget[Date] > MAX( tbl_Expenses[Date] )))
 
Upvote 0
Hi Scott,

Could you explain how do I use this formnula and how I should reference it ? Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Add content to empty cells in column - macro
Replies
6
Views
85
AFZAL SOHAIL
A
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
607
awoohaw
awoohaw
MrDB4Excel
  • Solved
Get the Correct Month
2
Replies
10
Views
173
Peter_SSs
Peter_SSs
R
  • Question Question
VBA Code to fetch data every particular day in a month
Replies
2
Views
196
ripdaman
R
Sandeep Warrier
  • Question Question
Calculate No. of Working Days based on Shift Pattern
Replies
2
Views
158
Tetra201
Tetra201

Forum statistics

Threads
1,224,878
Messages
6,181,527
Members
453,053
Latest member
DavidKele

Share this page

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

Which adblocker are you using?

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