Microsoft Office Forums How may I combine separate workbooks

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-02-2019, 10:00 AM
whitland whitland is offline How may I combine separate workbooks Windows 7 64bit How may I combine separate workbooks Office 2016
Novice
How may I combine separate workbooks
 
Join Date: Sep 2011
Posts: 8
whitland is on a distinguished road
Default How may I combine separate workbooks

I have three separate Excel workbooks, each with one worksheet. There are links between some of the content of the three (data modified in a cell in the sheet in book one is reflected to a cell in the sheet in book two for example). Is there any way in which I could combine the separate sheets into one workbook so that I would only need to open one workbook to see and work on all three sheets, without the data links being broken, or do I need to copy the sheets to book one and then recreate the links manually?
Reply With Quote
  #2  
Old 06-02-2019, 01:00 PM
Alansidman's Avatar
Alansidman Alansidman is offline How may I combine separate workbooks Windows 10 How may I combine separate workbooks Office 2019
Novice
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 24
Alansidman is on a distinguished road
Default

You could load each of the sheets into Power Query providing that they are all arranged exactly the same. Once in PQ, you could append each to the other.

Here is a tutorial

https://www.powerquery.training/port...ta-from-files/
Reply With Quote
  #3  
Old 06-03-2019, 02:37 AM
whitland whitland is offline How may I combine separate workbooks Windows 7 64bit How may I combine separate workbooks Office 2016
Novice
How may I combine separate workbooks
 
Join Date: Sep 2011
Posts: 8
whitland is on a distinguished road
Default

Thank you for your reply to my query. I will try Power Query as you suggest. Have to say that it does look somewhat complicated so may take some serious thought processes!!
Reply With Quote
  #4  
Old 06-03-2019, 04:07 AM
ArviLaanemets ArviLaanemets is offline How may I combine separate workbooks Windows 8 How may I combine separate workbooks Office 2016
Expert
 
Join Date: May 2017
Posts: 448
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

Quote:
Originally Posted by whitland View Post
Is there any way in which I could combine the separate sheets into one workbook so that I would only need to open one workbook to see and work on all three sheets, without the data links being broken, or do I need to copy the sheets to book one and then recreate the links manually?
You can create a 4rth workbook, and read data from your 3 existing workbooks into new one. You may have 3 separate worksheets with linked data in it, or you can combine data from different workbooks - it depends what you want, and how you read data.

To read data from another workbook you can use:
a) Links to fields in another workbook. Open both workbooks, activate source workbook and select a field with data, then activate target workbook and right-click on cell you want to link with source workbook cell, select from dropdown menu "Paste Special", and in Paste Special window click on PasteLink. Until the source workbook remains opened, the created formula in target workbook refers to source workbook. When you close the source workbook, the file path for source workbook is added to link.

The formula will be refreshed whenever you open the target file, or when you press F9. The source file can remain closed, and other users can use it without any restrictions. In case the source workbook is moved to another location, and you didn't do this opening both source and target workbooks and the saving the source workbook to new location, you can edit links selecting Edit Links from Data menu.

Plus side of links is an easy way to create them, and to manage them. The minus is, that links do not adjust automatically whenever new data are added into source workbook. And the result file may get slow too fast when the number of linked cells is rising.

b) You can create an ODBC query to read data from another workbook. It is more complicated to create a query, and in case you use 32-bid Excel on 64-bit computer you have to write an Open event which rewrites the query when you open the workbook (and because you have a code to run, you have to save the workbook as macro-enabled one). But once you set it up, query data read into target workbook are like any data entered directly - no formulas are used.

A query can also set to be runned on open. Or after certain interval (but I don't advice to use that feature). And you can refresh it manually at any time (for this, there are several options).
Reply With Quote
  #5  
Old 06-05-2019, 07:59 AM
Lugh's Avatar
Lugh Lugh is offline How may I combine separate workbooks Windows 10 How may I combine separate workbooks Office 2016
Advanced Beginner
 
Join Date: May 2019
Location: USA
Posts: 55
Lugh is on a distinguished road
Default

Quote:
Originally Posted by whitland View Post
without the data links being broken
Does Global Replace work with links?

If yes, then you can combine the sheets into one book, and replace all the link references to outside books with references to the local book. Should only need one replace operation per sheet—if it works.
__________________
Windows 10 Home x64 1803, Office 365 monthly channel
Main MSOs: Word, Excel, Access, OneNote, Outlook
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Possible to combine 3 different workbooks? markg2 Excel 5 11-14-2016 12:36 AM
How do I separate an address into separate cells Austcart Excel 2 01-18-2015 03:48 PM
Office 2008 workbooks into Office 2011 workbooks nfotx Excel 0 12-07-2014 04:22 PM
How may I combine separate workbooks Can I use the filter function to separate letters into separate files? drhauser Mail Merge 2 12-14-2011 02:18 PM
Any easy way to separate a Word document into separate files? SamHelm Word 0 08-21-2010 05:29 AM


All times are GMT -7. The time now is 10:46 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft