#1
|
|||
|
|||
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? |
#2
|
||||
|
||||
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/ |
#3
|
|||
|
|||
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!!
|
#4
|
|||
|
|||
Quote:
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). |
#5
|
||||
|
||||
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. |
|
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 |
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 |