I created a 10 spreadsheets for 10 companies that include 25 columns of info each and multiple tabs.
I need to create a master spreadsheet, which contains info from all 10 companies but only 10 of the columns are necessary. I want to ensure that if a change is made in the master spreadsheet in one of the columns for company "A", that change is reflected in their personal spreadsheet as well, pretty much syncing info typed in one worksheet to a spot in another.
Is that possible?
Thank you,
Zanna
Last edited by Zan03; 02-11-2010 at 03:27 PM.
Possible yes...messy though. Sounds like you're saying the data in the sub-sheets is more complete. That would force me to suggest your "Master" sheet shouldn't be an input sheet, it should be a report.
Changes should be in the most complete database, smaller subsets are created from that.
Also, I'm thinking the consolidated report created from all the sub-sheets would require VBA to be the most robust, something else to consider.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Thanks for the reply!
That's exactly what I'm looking for - a report.
Is there a way to do it without VBA? As i'm not too familiar with Macros...
Without VBA, probably not without major additions to your workbook. Let's take a look.
Click GO ADVANCED and use the paperclip icon to post up your workbook.
Thanks, I've attached an example.
As you'll see, each column has a corresponding tab.
For the "Master report" I'll need data from column A-i.
Any help is definitely appreciated!
Last edited by Cutter; 07-15-2012 at 11:07 AM. Reason: Removed whole post quote
I don't see how any of the sub sheets can serve to reflect data from the MASTER. None of the sheets share any similarities in layout/content. None of the sub-sheets have any sample data representing the cross-referencing you're trying to achieve.
The spreadsheet attached was the subsheet that i would like the master to be synced to.
Please see attachment.
The master only has columns A-I where the one i previously attached was A-W.
I have a spreadsheet per company similar to the file "syncing" (total of 10 spreadsheets) but i would like to take columns A-I from each spreadsheet and sync them to one master.
Sorry if I'm not explaining everything properly.
Thanks again for your help.
For no VBA, I'd suggest 1 sheet per sub-workbook in the Master workbook. In the first workbook, put this formula in A2:
='[Syncing.xls]Master Building List'!A2
Copy it down and across as far as you'd like (not too much, just what you need).
One the second sheet, just change the workbook reference and repeat.
This will give you a reflection of the values in the "master" sheet of the sub-workbooks. This will be a one-way reflection, you cannot change anything in your consolidation workbook and expect it to flow back, all that would happen is you would break the formula that links.
========
If you decided you had to have it all on one sheet, you could stack those formulas linking to each workbook on one sheet. The first formula goes in A2 and is copied across and down 100 rows or so. Then start again at A102 for the next workbook. Repeats.
Then you can turn on the AutoFilter for this sheet and filter column A for all values the Does Not Equal: 0
Last edited by JBeaucaire; 02-11-2010 at 02:51 PM.
So i cannot change the master in hopes that the subsheets will reflect the change but if I change something in the original sheet would the Master reflect the change?
How complicated would it be to use VBA? Do you have any pointers on where i can go (which website) for assistance in creating the macro?
That would be a bit of work and then of course you have to maintain it over time. You sure you want to go down that path?
Mark this thread as solved and start a different one in the Programming forum if you really want to open that can 'o worms.
EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].
(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)
Thank you for your help JBeaucaire!
Hi.
I'm sure you have your problem taken care of by now, but if you are still having problems or your solution requires too much maintenance, here is the way I do it.
In the sub-sheet, select the cell you wish to link with your master spread sheet.
1. Then type in the "=" sign without hitting the enter key.
2. Now go the master and select the cell you want to link with the sub-sheet and hit the enter key.
3. Voila! The cell in the sub-sheet will now always be linked with the chosen cell in the master. Do this for each cell you want linked.
If your sub-sheet has exactly the same layout as the master sheet, you can copy the first lined cell in the sub-sheet and past it anywhere you want the data from the master sheet to appear. This will work only if the layout of all the sheets you want linked with the master have exactly the same layout.
Example:
• A1 in the master sheet is the one you want duplicated.
• Go to your sub-sheet sheet and select cell A1 and Type the = sign without hitting the enter key.
• Go to the master and click on cell A1 then hit enter.
The cell A1 in the sub-sheet sheet will now always be linked with A1 in the master sheet as in the first example. Now you can edit the sub-sheet and make any cell on that sheet link with the same cell on the master sheet simply by copying the cell A1 in the sub-sheet and past it anywhere on the sub-sheet you want linked with the same cell in the master. You can even copy the A1 cell in the sub-sheet sheet and then select any range of columns and rows you want linked with the same cells in the master, and then paste. All of the cells you just pasted in the sub-sheet sheet will now automatically be linked with the same cells in the master. You can select a block of cells, or an entire column or row.
Another easy way to link a sheet with the master is to copy the A1 cell in the sub-sheet and then select the entire sheet and paste. Now the entire sheet will be linked with the entire master. Then you can edit the individual cells of the sub-sheet to personalize it. These changed cells will not be linked with the master.
An easier way is to create a master sub-sheet exactly as you want it with its own data and the linked data from the master as described above. Then you can simply copy the sub-master into a new sheet and the new sheet will only require the personalization for that sheet.
You may have some small problems with formatting. These methods will not copy formatting, because it is only a reference to the cell in the master, not a copy. It must be manually formatted. For the most part you can copy all of the cells in the master and select cell A1 on the sub-sheet and paste special/formatting. Then the sub-sheet will have the same formatting as the master. However nothing is really easy/perfect in MS software. If a cell in the master is blank then the linked cell in the sub-sheet will probably contain a zero. These cells will require a little extra formatting. But that’s the beauty of having a master sub-sheet. You only have to fix this problem once. When you copy the master sub-sheet and paste it into the new sub-sheet, it will be perfectly formatted.
One more tip. If the layout of all your sheets is identical, and you have the entire sub-sheet master linked to EVERY cell on the master, any changes you make to the master such as adding data into cells not previously used, then all of your sub-sheets will contain the new changes. However as above, the newly used cells in the sub-sheet will not reflect the formatting of the cell in the master.
I hope this helps you.
Bill
Good afternoon. I was researching how to sync from one work book to another...I have read and re-read all these post but I'm still not understanding (or accomplishing) the desired effect. can I snyc certain columns from one file on my computer and have it automatically update another file?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks