Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 02-14-2019, 04:09 AM
Joanne Joanne is offline Windows 7 32bit Office 2010 32bit
Advanced Beginner
 
Join Date: Jul 2013
Posts: 32
Joanne is on a distinguished road
Default Consolidate data from different Excel files

I have four Excel files containing data - Jan to Mar, Apr to Jun, etc. Is it possible to consolidate this data into one annual spreadsheet without copying and pasting?



Many thanks
Joanne
Reply With Quote
  #2  
Old 02-14-2019, 08:13 AM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 148
Marcia is on a distinguished road
Default

Try the consolidate tool under Data. I use that to consolidate payrolls from January to December.
Reply With Quote
  #3  
Old 02-14-2019, 08:46 AM
Joanne Joanne is offline Windows 7 32bit Office 2010 32bit
Advanced Beginner
 
Join Date: Jul 2013
Posts: 32
Joanne is on a distinguished road
Default consolidate spreadsheets

Hi Marcia

Thank you for your reply. I saw the Consolidate option and have Googled it, but still can't work out how to use it

I will have another go and if it still doesn't work for me - I'll be back!

Regards,
Jo
Reply With Quote
  #4  
Old 02-14-2019, 08:51 AM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 148
Marcia is on a distinguished road
Default

Open a blank sheet, go to data tab, select consolidate. A dialogue box will pop out, click the browse button, select your January file. I presume that you have headers in your files so check the "row headers", click "add". Repeat the same process until the last sheet that you want to add in the consolidated data. To make the consolidation process easier, remove all merged cells.
Reply With Quote
  #5  
Old 02-14-2019, 08:57 AM
Joanne Joanne is offline Windows 7 32bit Office 2010 32bit
Advanced Beginner
 
Join Date: Jul 2013
Posts: 32
Joanne is on a distinguished road
Default

I've tried that but I keep getting the message "Consolidation reference is not valid"
Reply With Quote
  #6  
Old 02-14-2019, 09:11 AM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 148
Marcia is on a distinguished road
Default

Did you select the range? do not include the row labels.
Reply With Quote
  #7  
Old 02-14-2019, 09:16 AM
Joanne Joanne is offline Windows 7 32bit Office 2010 32bit
Advanced Beginner
 
Join Date: Jul 2013
Posts: 32
Joanne is on a distinguished road
Default

I've opened a new sheet, clicked on Consolidate, Browse and click on the first of my files. The file path appears in the Reference box. When I click on Add, the error message appears.
Reply With Quote
  #8  
Old 02-14-2019, 04:44 PM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 148
Marcia is on a distinguished road
Default

Our internet signal flew off somewhere last night. After opening your file through the browse buttton, click the mouse at the end of the file path, after the exclamation mark, then go to the file and select the range, say b1:ac74. Add.
Attached Images
File Type: png Capture.PNG (40.3 KB, 4 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Move & Consolidate Data meggenm Excel Programming 1 08-27-2017 12:54 PM
Add data from two Excel Files to Word Doc Template nivolaped Word 4 04-10-2016 04:23 PM
Merge the data from different excel files. Sandhya Excel Programming 2 10-10-2015 02:53 PM
Consolidate data from different excel sheets Sandhya Excel Programming 1 10-10-2015 12:28 AM
How to replicate, consolidate and count cell data in excel for data consoldation. Loran Excel 7 06-05-2014 01:07 AM


All times are GMT -7. The time now is 10:48 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft