Link between two worksheets

Microsoft Microsoft office excel 2007 ac...
October 20, 2011 at 09:58:01
Specs: Windows 7, 4 Gb
I want to have a central workbook that contains some worksheets from other secondary books. The idea is to have totally sincronized workbook in format and contents. How i can do that without macros?

See More: Link between two worksheets

Report •

October 20, 2011 at 10:30:21
I don't understand what you are asking for.

You can't have a workbook that "contains" worksheets from other workbooks...any worksheets "contained" in a workbook are in that workbook.

I suppose you could use a macro to copy/move sheets from another workbook into the active workbook.

Please explain in some more detail what you are trying to accomplish.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

October 21, 2011 at 07:08:45
I want to have a central file that is sincronized permanently (like a link) with other files that are in other folders and are edited by another people. The idea is to have some worksheets totally (format and contents) in the central file.

Report •

October 21, 2011 at 09:34:01
re: "The idea is to have some worksheets totally (format and contents) in the central file."

Once again, this is the requirement that I have an issue with. Perhasp it is just the way that you are wording the requirement.

Let's start with a few basic facts, just so that you and I are on the same page. Maybe you know all of this, but bear with me...I'm just setting some ground rules for our discussion. Once we agree on these items, we can then discuss your requirements based on this information, using the proper terms.

1 - An Excel Workbook is a file that must contain at least 1 worksheet. The workbook may contain multiple worksheets, the number of which is dependent on what version of Excel you are running.

2 - If a worksheet is "physically" part of a given workbook it can not be "physically" part of any other workbook. In other words, a specific worksheet can not be "shared" by multiple files. It can only exist in one workbook.

3 - Links can be created in a worksheet such that data in other worksheets - even worksheets in other workbooks - can be reflected in the worksheet with the links.


Links between worksheets in the same workbook

If placed in Sheet1!A1, this formula will display the value from Sheet2!A1.


Links between worksheets that reside in different workbooks

This formula will look different depending on whether the source file is open or closed at the time. Excel will adjust the formula automatically by adding the path when the source file is closed.

If the source file is open:


If the source file is closed:

='\\Drive_Designation\user_name\MY DOCUMENTS\[Book2.xls]Sheet2'!$A$1

4 - A workbook can be shared so that multiple users can edit it at the same time. Restrictions exists as to what users can do in a shared workbook. Some features are disabled.

With all that said, if you are trying to reflect data from multiple worksheets that are parts of other workbooks into a "central" workbook, I suggest this:

1 - Make a copy of each worksheet that you need to "mirror" in the central workbook. An easy way to accomplish this is to copy the sheet from the source to the central workbook, which will bring over all of the formats, as well as the data and formulas.

2 - Populate the cells in the copied worksheet (the one in the central workbook) with formulas that link the cells to the corresponding cells in the source workbook. This can be done by entering an equal sign in a cell in the central workbook and then clicking on the cell in the source workbook. This can also be done via the auto-fill handle, but just keep in mind that if you have too many links to external sources, your workbook could become very sluggish.

Your other option it to set up the central workbook so that it physically contains all of the other worksheets from all of the other workbooks and then Share the central workbook.

Before you decide on the shared workbook solution, please refer to the information at this site which details the features that are not available in a shared workbook.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

Ask Question