Working with Multiple Excel Files

Resolved · Urgent Priority · Version 2007

Sun 12 Feb 2012, 12:54 Delegate Ajibola said...

Ajibola has attended:
Project Intro Intermediate course
Excel Intermediate course
Excel Advanced course
Excel Dashboards for Business Intelligence course

Working with Multiple Excel Files

How can I make for instance ranges D1:D5 of about 5 different Excel files to show in a separate Excel file. I want the separate Excel file to consolidate D1:D5 of those 5 Excel Files. See below for example:

File1 File2 File3 File4 File5
500 501 502 509 500
300 302 306 308 309
100 105 107 106 105
430 438 439 431 434
120 129 120 122 123

Mon 13 Feb 2012, 08:42 Trainer Mark said...

RE: Working with Multiple Excel Files

Hello Ajibola,

It is possible to consolidate the data as you request without coding, as Excel has this type of facility built in.

Open the Files that contain the data, then in the file that you wish to consolidate all the data, select the Data Ribbon, Data Tools Section, Consolidate button.
In the dialog Browse/Locate the data from the files, Click the Add button.
You can also specify to include headings, or have the data linked. Click OK and the data will be consolidated.

I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Mark
Microsoft Office Specialist Trainer

Mon 13 Feb 2012, 11:07 Delegate Ajibola said...

RE: Working with Multiple Excel Files

That didn't work. I don't want to consolidate entire sheets from all other files but specified ranges from them. Even when I tried to insert files into the file I wanted for consolidation I received "Consolidation reference is not valid".

Mon 13 Feb 2012, 15:36 Trainer Mark said...

RE: Working with Multiple Excel Files

Hello Ajibola,

In the dialog for Consolidating ranges, you specify the path to the workbook, then you have to select a range, or type it in. It will look like this:

C:\My Workbook.xlsx'!H6:K11

Browse for the file that you have open, then manually type the range at the end, in my example H6:K11
Then click Add.
If you are still having problems refer to the Help files on how to set up Consolidate.

I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Mark
Microsoft Office Specialist Trainer

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Sum Up All the Values in A Column

If you want to quickly calculate the Summed values of all cells in a column in Excel 2003 normally you would use the SUM formula. (eg if you wanted to calculate the values in Column C rows 10 to 25) the formula would be:

=SUM(C10:C25)

However, if you keep adding values to column C you would keep having to modify the above SUM formula which can get quite annoying.

To get around this you can sum all the values in a column using the following formula:

=SUM(COLUMN:COLUMN)

Which, in our example, would be:

=SUM(C:C)

NOTE You cannot place this formula in column C, or else Excel 2003 will show a circular reference error.

The formula must be placed in any other column, EXCEPT the one being calculated.