Trying to add up Data from Several Excel Files

[blameHim]

Hello,
I am using XL07 and trying to add up totals from several expense reports in one worksheet summary. Each expense report is a separate file. Cells A14 - A27 are the expense description, like Airfare, breakfast, lunch, etc. Cells I14 - I27 are the totals for each row.

Example of the worksheet layout:
Rows 1-7 consist of Trip info, I do not need this data
Row 8 has Mon-Sunday listed horizontally starting with B8
Rows 10-13: Billing reference (useless to me)
Rows 14-27: noted above.

There are daily totals for each expense which are summed up for each expense in Column I by the "SUM" formula.

My goal is to summarize how much was spent for the year for each expense category. Thank you for your time and consideration.

Thanks,
MP

PS.
I tried to download the HTML maker, but the page could not be found

 

[hiker95]

blameHim,

Welcome to the MrExcel forum.

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

Or, you can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[blameHim]

Thanks Hiker95 for the HTML info.

I am trying to get the sum each of my expense categories from over 30 separate expense reports in order to see how much was spent on each category. The categories are listed in A14-27. Each expense report looks the same as the table below. They are stored as separate excel files. I have XL07. Thanks

MP

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	DATE:	6-Mar-08		*	*	*	*	*	*
2	NAME:	*	*	*	VENDOR/AGENCY:	*	*	*	*
3	TRIP TO:	Midwest City, OK	*	*	SITE LOCATION(s):	1303	*	*	*
4	PURPOSE:	*	*	*	DATES OF TRIP: *	2-Jan-08	THROUGH	3-Jan-08	*
5	PROJECT(S):	*		TOTAL HRS. ON SITE:	6.5	*	*	END DATE	*
6	TOTAL TRAVEL HOURS:	*	13	SUPERVISOR:	*	*	EXT:	*	*
7	*	*	*	*	*	*	*	*	*
8	DESCRIPTION	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY	SUNDAY	TOTALS
9	DATE:	12/31/07	01/01/08	01/02/08	01/03/08	01/04/08	01/05/08	01/06/08	*
10	EarlyACS: 393-3161	*	*	*	*	*	*	*	*
11	IMPROVE-IT: 393-3423	*	*	*	*	*	*	*	*
12	ASCEND: 393-3702	*	*	100	100	*	*	*	*
13	TRACER: 393-3738	*	*	*	*	*	*	*	*
14	AIR FARE	*	*	$597.59	*	*	*	*	$597.59
15	TRAVEL AGENCY FEE	*	*	$33.00	*	*	*	*	$33.00
16	ROOM	*	*	*	$192.45	*	*	*	$192.45
17	BRKFAST	*	*	*	*	*	*	*	$0.00
18	LUNCH	*	*	$5.13	*	*	*	*	$5.13
19	DINNER	*	*	$37.76	$2.17	*	*	*	$39.93
20	TIPS	*	*	$2.00	$2.00	*	*	*	$4.00
21	RENT CAR	*	*	*	$54.90	*	*	*	$54.90
22	GAS	*	*	*	$9.61	*	*	*	$9.61
23	TAXI/Transportation	*	*	*	*	*	*	*	$0.00
24	PHONE	*	*	*	*	*	*	*	$0.00
25	MILEAGE@50.5/Mi.	*	*	*	$17.36	*	*	*	$17.36
26	PARK/TOLL	*	*	*	$22.00	*	*	*	$22.00
27	INTERNET	*	*	*	*	*	*	*	$0.00
28	DAILY TOTAL:	$0.00	$0.00	$675.48	$300.49	$0.00	$0.00	$0.00	$975.97
29	*	*	*	*	*	*	*	TOTAL EXPENSES:	$975.97
Sheet1

 

[hiker95]

blameHim,

Thanks for the screenshot.

Can I have screenshots of two other expense reports for testing.

And, can I have a screenshot of your Summary worksheet.

What is the full path to where the over 30 separate expense reports?

 

[blameHim]

Hiker95, the full path is C:\Documents and Settings\Michael\My Documents\Inclusion Research\Expense\2008
Here is a screenshot of my summary page: I tried to get the totals using the consolidate feature, but I could not get the data to stay in the same row, but it did not work. My ultimate goal is to just get the sum for each expense from the all of the spreadsheets. Thanks again for looking into this.

Excel Workbook
	A	B
1	*	2008 Total
2	AIR FARE	*
3	TRAVEL AGENCY FEE	*
4	ROOM	*
5	BRKFAST	*
6	LUNCH	*
7	DINNER	*
8	TIPS	*
9	RENT CAR	*
10	GAS	*
11	TAXI/Transportation	*
12	PHONE	*
13	MILEAGE@50.5/Mi.	*
14	PARK/TOLL	*
15	INTERNET	*
Sheet3

 

[hiker95]

blameHim,


Sample workbooks for testing:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	DATE:	27-Feb-08
2	NAME:				VENDOR/AGENCY:
3	TRIP TO:	Midwest City, OK			SITE LOCATION(s):	1303
4	PURPOSE:				DATES OF TRIP: *	2-Jan-08	THROUGH	3-Jan-08
5	PROJECT(S):			TOTAL HRS. ON SITE:	6.5			END DATE
6	TOTAL TRAVEL HOURS:		13	SUPERVISOR:			EXT:
7
8	DESCRIPTION	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY	SUNDAY	TOTALS
9	DATE:	12/31/2007	1/1/2008	1/2/2008	1/3/2008	1/4/2008	1/5/2008	1/6/2008
10	EarlyACS: 393-3161
11	IMPROVE-IT: 393-3423
12	ASCEND: 393-3702			100	100
13	TRACER: 393-3738
14	AIR FARE			$597.59		$50.00			$647.59
15	TRAVEL AGENCY FEE			$33.00		$50.00			$83.00
16	ROOM				$192.45	$50.00			$242.45
17	BRKFAST								$0.00
18	LUNCH			$5.13		$50.00			$55.13
19	DINNER			$37.76	$2.17	$50.00			$89.93
20	TIPS			$2.00	$2.00	$50.00			$54.00
21	RENT CAR				$54.90	$50.00			$104.90
22	GAS				$9.61	$50.00			$59.61
23	TAXI/Transportation								$0.00
24	PHONE								$0.00
25	MILEAGE@50.5/Mi.				$17.36	$50.00			$67.36
26	PARK/TOLL				$22.00	$50.00			$72.00
27	INTERNET								$0.00
28	DAILY TOTAL:	$0.00	$0.00	$675.48	$300.49	$500.00	$0.00	$0.00	$1,475.97
29								TOTAL EXPENSES:	$1,475.97
30
Sheet1

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	DATE:	6-Mar-08
2	NAME:				VENDOR/AGENCY:
3	TRIP TO:	Midwest City, OK			SITE LOCATION(s):	1303
4	PURPOSE:				DATES OF TRIP: *	2-Jan-08	THROUGH	3-Jan-08
5	PROJECT(S):			TOTAL HRS. ON SITE:	6.5			END DATE
6	TOTAL TRAVEL HOURS:		13	SUPERVISOR:			EXT:
7
8	DESCRIPTION	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY	SUNDAY	TOTALS
9	DATE:	12/31/2007	1/1/2008	1/2/2008	1/3/2008	1/4/2008	1/5/2008	1/6/2008
10	EarlyACS: 393-3161
11	IMPROVE-IT: 393-3423
12	ASCEND: 393-3702			100	100
13	TRACER: 393-3738
14	AIR FARE			$597.59					$597.59
15	TRAVEL AGENCY FEE			$33.00					$33.00
16	ROOM				$192.45				$192.45
17	BRKFAST								$0.00
18	LUNCH			$5.13					$5.13
19	DINNER			$37.76	$2.17				$39.93
20	TIPS			$2.00	$2.00				$4.00
21	RENT CAR				$54.90				$54.90
22	GAS				$9.61				$9.61
23	TAXI/Transportation								$0.00
24	PHONE								$0.00
25	MILEAGE@50.5/Mi.				$17.36				$17.36
26	PARK/TOLL				$22.00				$22.00
27	INTERNET								$0.00
28	DAILY TOTAL:	$0.00	$0.00	$675.48	$300.49	$0.00	$0.00	$0.00	$975.97
29								TOTAL EXPENSES:	$975.97
30
Sheet1

Your Summary workbook, Sheet3 before the macro:

Excel Workbook
	A	B	C	D
1
2	AIR FARE
3	TRAVEL AGENCY FEE
4	ROOM
5	BRKFAST
6	LUNCH
7	DINNER
8	TIPS
9	RENT CAR
10	GAS
11	TAXI/Transportation
12	PHONE
13	MILEAGE@50.5/Mi.
14	PARK/TOLL
15	INTERNET
16
17
Sheet3

Your Summary workbook, Sheet3 after the macro:

Excel Workbook
	A	B	C	D
1		27-Feb-08	6-Mar-08	TOTALS
2	AIR FARE	647.59	597.59	$1,245.18
3	TRAVEL AGENCY FEE	83.00	33.00	$116.00
4	ROOM	242.45	192.45	$434.90
5	BRKFAST	0.00	0.00	$0.00
6	LUNCH	55.13	5.13	$60.26
7	DINNER	89.93	39.93	$129.86
8	TIPS	54.00	4.00	$58.00
9	RENT CAR	104.90	54.90	$159.80
10	GAS	59.61	9.61	$69.22
11	TAXI/Transportation	0.00	0.00	$0.00
12	PHONE	0.00	0.00	$0.00
13	MILEAGE@50.5/Mi.	67.36	17.36	$84.72
14	PARK/TOLL	72.00	22.00	$94.00
15	INTERNET	0.00	0.00	$0.00
16			TOTAL EXPENSES:	$2,451.94
17
Sheet3

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub GetMyData()
' hiker95, 06/23/2010
Dim myDir As String, fn As String, sn As String
Dim NC As Long, CName As String
Application.ScreenUpdating = False

'myDir = "C:\TestData"  'for testing

myDir = "C:\Documents and Settings\Michael\My Documents\Inclusion Research\Expense\2008"

sn = "Sheet1"
fn = Dir(myDir & "\*.xlsx")

With ThisWorkbook.Sheets("Sheet3")
  NC = .Cells(2, Columns.Count).End(xlToLeft).Column + 1
  CName = Replace(Cells(1, NC).Address(0, 0), 1, "")
  .Columns("B:" & CName).Delete
End With
Do While fn <> ""
  If fn <> ThisWorkbook.Name Then
    With ThisWorkbook.Sheets("Sheet3")
      NC = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
      If NC = 1 Then NC = 2
      With .Cells(1, NC)
        .Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!B1"
        .Value = .Value
        .NumberFormat = "d-mmm-yy"
      End With
      With .Cells(2, NC)
        .Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!I14"
        .Value = .Value
      End With
      With .Cells(3, NC)
        .Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!I15"
        .Value = .Value
      End With
      With .Cells(4, NC)
        .Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!I16"
        .Value = .Value
      End With
      With .Cells(5, NC)
        .Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!I17"
        .Value = .Value
      End With
      With .Cells(6, NC)
        .Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!I18"
        .Value = .Value
      End With
      With .Cells(7, NC)
        .Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!I19"
        .Value = .Value
      End With
      With .Cells(8, NC)
        .Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!I20"
        .Value = .Value
      End With
      With .Cells(9, NC)
        .Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!I21"
        .Value = .Value
      End With
      With .Cells(10, NC)
        .Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!I22"
        .Value = .Value
      End With
      With .Cells(11, NC)
        .Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!I23"
        .Value = .Value
      End With
      With .Cells(12, NC)
        .Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!I24"
        .Value = .Value
      End With
      With .Cells(13, NC)
        .Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!I25"
        .Value = .Value
      End With
      With .Cells(14, NC)
        .Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!I26"
        .Value = .Value
      End With
      With .Cells(15, NC)
        .Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!I27"
        .Value = .Value
      End With
      .Range(.Cells(2, NC), .Cells(15, NC)).NumberFormat = "#,##0.00"
      .Range(.Cells(1, NC), .Cells(15, NC)).Columns.AutoFit
    End With
  End If
  fn = Dir
Loop
With ThisWorkbook.Sheets("Sheet3")
  NC = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
  With .Cells(1, NC)
    .Value = "TOTALS"
    .Font.Bold = True
    .HorizontalAlignment = xlCenter
    .Font.Name = "Arial"
    .Font.Size = 10
    .Font.Underline = xlUnderlineStyleSingle
  End With
  With .Cells(16, NC - 1)
    .Value = "TOTAL EXPENSES:"
    .Font.Name = "Arial"
    .Font.FontStyle = "Bold"
    .Font.Size = 10
    .HorizontalAlignment = xlRight
  End With
  CName = Replace(Cells(1, NC - 1).Address(0, 0), 1, "")
  .Cells(2, NC).Formula = "=SUM(B2:" & CName & "2)"
  .Cells(2, NC).Copy .Cells(3, NC).Resize(13)
  CName = Replace(Cells(1, NC).Address(0, 0), 1, "")
  .Cells(16, NC).Formula = "=SUM(" & CName & "2:" & CName & "15)"
  With .Range(CName & "2:" & CName & "16")
    .NumberFormat = "$#,##0.00"
    .Font.Name = "Arial"
    .Font.FontStyle = "Bold"
    .Font.Size = 10
  End With
  .Range(CName & "1:" & CName & "16").Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub

Then run the "GetMyData" macro.

 

[tkhouse]

HI,
I have a very similar situation. I have 9-10 people who track their daily activities on separate spreadsheets and turn them in at the end of the month. The format is the same for each spreadsheet or at least started out the same although some may periodically add lines for notes/information. Basically, I need to consolidate the daily and monthly totals from each spreadsheet into a master spreadsheet that looks the same as the original. I am open to changing the format if necessary in order to facilitate the process. I have been doing research but code is Greek to me and I don't know where to begin.

 

[hiker95]

tkhouse,

Welcome to the MrExcel forum.


It would be better if you started your own new post.

When you do, add a link to this post:
http://www.mrexcel.com/forum/newreply.php?do=newreply&noquote=1&p=2570960



And:

What version of Excel are you using?

Please attach screenshots of your workbook or a sample workbook that accurately portrays your current workbook on one sheet, and what it should look like 'After' on another sheet. This makes it much easier to see exactly what you want to do, as well as shows us whether there is a consistent number of rows between tables and such.

Here are three possible ways to post small (copyable) screen shots directly in your post:

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

or
RichardSchollar’s beta HTML Maker -...his signature block at the bottom of his post

or
Borders-Copy-Paste


Or, you can upload your workbook to www.box.net and provide us with a link to your workbook.