98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Working with Multiple Excel Files
Working with Multiple Excel Files
Resolved · Urgent Priority · Version 2007
Sun 12 Feb 2012, 12:54Delegate 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:42Trainer 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:07Delegate 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:36Trainer 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 information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Sum Up All the Values in A ColumnIf 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: |
