Need Help with Financial spreadsheet

[enjef]

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??

 

[tyija1995]

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

 

[tyija1995]

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.

 

[enjef]

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.

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]

 

[tyija1995]

Hey,

Can you total each category up on the individual sheets? Or does it have to left as is?