Need Help with Financial spreadsheet

E

enjef

New Member
Joined
May 14, 2019
Messages
2
Hi, I have a workbook that is meant to track travel costs across various departments. it has 11 department spreadsheets and one summary sheet. My issue is finding either a macro or function that will transfer the individual travel cost entries on the various sheets, to a single cost cell on the summary sheet and keep it as a running total for each dept sheet.
Can anyone help??
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Depends how your data is laid out on other tabs - you could consider using an INDIRECT function if the department sheets share a similar layout, hard to tell without knowing your workbook
 
Upvote 0
For example you could have something like this on your SUMMARY sheet:

[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]SUMMARY[/TD]
[TD]Dept1[/TD]
[TD]Dept2[/TD]
[TD]Dept3[/TD]
[TD]Dept4[/TD]
[TD]Dept5[/TD]
[TD]Dept6[/TD]
[TD]Dept7[/TD]
[TD]Dept8[/TD]
[TD]Dept9[/TD]
[TD]Dept10[/TD]
[TD]Dept11[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Travel[/TD]
[TD]59.99[/TD]
[TD]250.50[/TD]
[TD]29.99[/TD]
[TD]33.33[/TD]
[TD]355.00[/TD]
[TD]20.50[/TD]
[TD]495.00[/TD]
[TD]56.34[/TD]
[TD]414.40[/TD]
[TD]101.50[/TD]
[TD]29.40[/TD]
[TD]1845.95[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Food[/TD]
[TD]13.50[/TD]
[TD]4.99[/TD]
[TD]3.00[/TD]
[TD]28.20[/TD]
[TD]27.00[/TD]
[TD]2.50[/TD]
[TD]47.20[/TD]
[TD]560.50[/TD]
[TD]28.50[/TD]
[TD]99.00[/TD]
[TD]35.30[/TD]
[TD]849.69[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Drink[/TD]
[TD]6.99[/TD]
[TD]0.00[/TD]
[TD]4.50[/TD]
[TD]16.10[/TD]
[TD]13.00[/TD]
[TD]3.58[/TD]
[TD]3.20[/TD]
[TD]292.00[/TD]
[TD]39.40[/TD]
[TD]97.52[/TD]
[TD]47.40[/TD]
[TD]523.69[/TD]
[/TR]
</tbody>[/TABLE]

Where B2 holds the formula that can be copied across to L4:

=INDIRECT(B$1&"!"&CELL("address",$B1))

Assuming here that Travel/Food/Drink costs appear in cells B1/B2/B3 cells respectively on the Dept No. worksheets - again this is due to the layout I have tested on. But this is to give an idea of what can be achieved with INDIRECT.
 
Last edited:
Upvote 0
tyija1995 said:
For example you could have something like this on your SUMMARY sheet:

[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]SUMMARY[/TD]
[TD]Dept1[/TD]
[TD]Dept2[/TD]
[TD]Dept3[/TD]
[TD]Dept4[/TD]
[TD]Dept5[/TD]
[TD]Dept6[/TD]
[TD]Dept7[/TD]
[TD]Dept8[/TD]
[TD]Dept9[/TD]
[TD]Dept10[/TD]
[TD]Dept11[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Travel[/TD]
[TD]59.99[/TD]
[TD]250.50[/TD]
[TD]29.99[/TD]
[TD]33.33[/TD]
[TD]355.00[/TD]
[TD]20.50[/TD]
[TD]495.00[/TD]
[TD]56.34[/TD]
[TD]414.40[/TD]
[TD]101.50[/TD]
[TD]29.40[/TD]
[TD]1845.95[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Food[/TD]
[TD]13.50[/TD]
[TD]4.99[/TD]
[TD]3.00[/TD]
[TD]28.20[/TD]
[TD]27.00[/TD]
[TD]2.50[/TD]
[TD]47.20[/TD]
[TD]560.50[/TD]
[TD]28.50[/TD]
[TD]99.00[/TD]
[TD]35.30[/TD]
[TD]849.69[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Drink[/TD]
[TD]6.99[/TD]
[TD]0.00[/TD]
[TD]4.50[/TD]
[TD]16.10[/TD]
[TD]13.00[/TD]
[TD]3.58[/TD]
[TD]3.20[/TD]
[TD]292.00[/TD]
[TD]39.40[/TD]
[TD]97.52[/TD]
[TD]47.40[/TD]
[TD]523.69[/TD]
[/TR]
</tbody>[/TABLE]

Where B2 holds the formula that can be copied across to L4:

=INDIRECT(B$1&"!"&CELL("address",$B1))

Assuming here that Travel/Food/Drink costs appear in cells B1/B2/B3 cells respectively on the Dept No. worksheets - again this is due to the layout I have tested on. But this is to give an idea of what can be achieved with INDIRECT.
Click to expand...

Thanks for the reply:

The summary sheet is laid out as below. I need the data from the cost lines (Bus, taxi, Rail, Flights) to populate into the Summary sheet from the individual sheets as running totals. So in the example below, Bus would = 37
Taxi = 29 Rail = 131 Flights 233 - Is this possible using INDIRECT?

[TABLE="width: 448"]
<colgroup><col span="7"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Summary Sheet[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dept[/TD]
[TD]Month[/TD]
[TD]Bus[/TD]
[TD]Taxi[/TD]
[TD]rail[/TD]
[TD]Flights[/TD]
[TD]Total Cost[/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Individual Sheets[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dept 1[/TD]
[TD]Date[/TD]
[TD]Bus[/TD]
[TD]Taxi[/TD]
[TD]Rail[/TD]
[TD]Flights[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] 01/05[/TD]
[TD] 15[/TD]
[TD] [/TD]
[TD] 95[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] 05/05[/TD]
[TD] 08[/TD]
[TD] [/TD]
[TD] 36[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] 8/05[/TD]
[TD] 14[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]86 [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] 10/05[/TD]
[TD] [/TD]
[TD] 29[/TD]
[TD] [/TD]
[TD] 147[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hey,

Can you total each category up on the individual sheets? Or does it have to left as is?
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Subscription cost summary
Replies
1
Views
150
bobsan42
bobsan42
J
  • Locked
  • Question Question
Still need help on a daily summary sheet
Replies
1
Views
582
Fluff
Fluff
J
  • Question Question
Power Query editor OR VBA to extract data from Grants.gov
Replies
3
Views
294
Jagadeshrao
J
D
  • Question Question
Tally that shifts cells daily.
Replies
3
Views
688
portews
P
G
  • Question Question
VBA help - copy cells from specific table to specific table on another sheet.
Replies
0
Views
738
GardianAngelUK
G

Forum statistics

Threads
1,223,228
Messages
6,170,874
Members
452,363
Latest member
merico17

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