Help with a Workbook issue

N

NoelJ

New Member
Joined
Aug 8, 2019
Messages
2
Good morning
I have a workbook which has 9 individual Dept sheets to record travel costs. I am looking for help in calculating the total monthly cost on each sheet and transfering that figure into the correct month on the summary sheet.

[TABLE="width: 448"]
<colgroup><col width="64" style="width:48pt" span="7"> </colgroup><tbody>[TR]
[TD="width: 448, bgcolor: transparent, colspan: 7"]Travel Costs - Dept 1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Month[/TD]
[TD="bgcolor: transparent"]Bus[/TD]
[TD="bgcolor: transparent"]Train[/TD]
[TD="bgcolor: transparent"]Taxi[/TD]
[TD="bgcolor: transparent"]Flights[/TD]
[TD="bgcolor: transparent"]Car Hire[/TD]
[TD="bgcolor: transparent"]Total Cost[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Apr[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]42[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]195[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]247[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Apr[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]15[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]23[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Apr[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]May[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]13[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]66[/TD]
[TD="bgcolor: transparent, align: right"]79[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]May[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]May[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]26[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]352[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]378[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jun[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]25[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]30[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jun[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]25[/TD]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]45[/TD]
[TD="bgcolor: transparent, align: right"]93[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jul[/TD]
[TD="bgcolor: transparent, align: right"]15[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]15[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jul[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]198[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]215[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jul[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]24[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]27[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Aug[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]21[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]27[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Aug[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]75[/TD]
[TD="bgcolor: transparent, align: right"]93[/TD]
[TD="bgcolor: transparent, align: right"]172[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Sept[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent, align: right"]13[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Sept[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Sept[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent, align: right"]65[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]29[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]102[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Sept[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[TD="bgcolor: transparent, align: right"]42[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]66
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 448"]
<colgroup><col span="7"></colgroup><tbody>[TR]
[TD="colspan: 7"]Monthly Total Cost - Summary Sheet[/TD]
[/TR]
[TR]
[TD]Dept[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[/TR]
[TR]
[TD]Dept 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dept 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dept 3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dept 4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dept 5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dept 6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dept 7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dept 8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dept 9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]

[TR]
[TD]I am trying to search for the correct month entries, total them then transfer that figure to the summary sheet.
Is this possible or am I asking the impossible?
I have tried IF,SUMIF,DGET and VLOOKUP but nothing seems to work, but that could be me! Could you help at all?
Many thanks
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]

</tbody>[/TABLE]
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
How about


Book1
ABCDEFG
1MonthBusTrainTaxiFlightsCar HireTotal Cost
2Apr1042195247
3Apr81523
4Apr325
5May136679
6May22
7May26352378
8Jun52530
9Jun725164593
10Jul1515
11Jul89198215
12Jul32427
13Aug62127
14Aug47593172
15Sept91322
16Sept1414
17Sept86529102
18Sept2224266
Travel Costs - Dept 1



Book1
ABCDEFG
1DeptAprMayJunJulAugSep
2Dept 12754591232571990
3Dept 2
4Dept 3
5Dept 4
6Dept 5
7Dept 6
8Dept 7
9Dept 8
10Dept 9
Summary
Cell Formulas
RangeFormula
B2=SUMIF(INDIRECT("'Travel Costs - "&$A2&"'!A:A"),B$1,INDIRECT("'Travel Costs - "&$A2&"'!G:G"))
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
auto filling cells based on preset format
Replies
1
Views
35
HighAndWilder
H
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
124
flashgordie
F
B
  • Question Question
Chart help - rainfall data
Replies
2
Views
313
JV0710
J
J
  • Question Question
adding cells based on criteria across several columns
Replies
5
Views
999
Cubist
Cubist
S
  • Question Question
Problem with x-axis labels in excel chart
Replies
1
Views
459
sharshra
S

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

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