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:

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

<tbody>
</tbody>

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:

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

<tbody>
</tbody>

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?

		Summary Sheet
Dept	Month	Bus	Taxi	rail	Flights	Total Cost
Dept 1
Dept 2
Dept 3
Dept 4
	Individual Sheets
Dept 1	Date	Bus	Taxi	Rail	Flights
	01/05	15		95
	05/05	08		36
	8/05	14			86
	10/05		29		147

<colgroup><col span="7"></colgroup><tbody>
</tbody>

 

[tyija1995]

Hey,

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