|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:
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.