My assistant and I use the exact same excel sheet. We need 2 sheets that will sync because

[ReneNeyrey]

My assistant and I use the exact same excel sheet. It is a list of people and companies we do business with some basic information about them. It's all text, no formulas or links. We each make small changes during the course of a day. Often we BOTH have the sheet open at the same time. The sheet resides on our cloud service and when we both make changes and each save the file gets marked "Conflicted Copy."

If we hade 2 exact duplicates of the same sheet and they somehow synced, our problem would be solved. Is there a way to make them automatically sync? Syncing after saving is fine, it doesn't have to be real time.

Thank you in advance.

[6StringJazzer]

What is your cloud service? Sounds like Dropbox. If so, you are not really opening the same sheet at the same time, you are each opening your own copy of it. You can manage this using Office Online but that is probably a separate subscription from what you have.

If you each had your own copy, then a sync process could create/maintain a third master copy but this gets complicated. To even consider the feasibility we would need to have a copy of your file (with any private information removed). Please attach a sample here and we can see what could be done. The real problem here is if you each make different changes to the same data. Aside from that, you have to determine what event will trigger the sync process.

[ReneNeyrey]

We use a Microsoft Exchange service named Intermedia. One service they offer is "SecurSync." Everyones files are syncing in real time all the time.
I asked them about this and they (at least the tech I spoke with) had no solution. So, I thought there might be an excel solution.

I was hoping there could be 2 excel files. Name one List boss. name the other list assistant. And somehow make them sync any changes made by either party.
I read there is a way to link 2 spreadsheets but it was referring to a particular cell, not a whole sheet.

Thanks for your help, 6StringJazzer. Can you see my attachment? I attached an excel file.

I just removed and replaced the attachment. I attached the wrong one. please delete it.

[6StringJazzer]

To be clear, Intermedia is a third-party service, not Microsoft. SecuriSync advertises "Co-editing in real-time". So what you want to do ought to work. If an Intermedia tech told you there is no solution then either they are not informed, or this is false advertising, or you need some other feature that is not provided.

I'll take a look at your file and see what I can suggest.

[6StringJazzer]

This file has a very loose structure so is going to be very difficult to sync two copies. I'll get back after further review.

[ReneNeyrey]

Thank you, 6StringJazzer
In that case, I need to call intermedia.

[6StringJazzer]

If this were a table, and every row had the same data, it would be more straightforward. But you have random formatting, multiple kinds of data in the same column, varying number of rows for the same item. It's just completely random. It might be possible to write a sync for this, but it would take me so much time I can't do it for free.

However, here are some examples problems with syncing this type of data. The strategy would be to open both files and compare every row in each file.

• When a row is found in File A but that row is not in File B, the sync process would not know if it was added in File A, or deleted from File B.
• The process would have to look at every column in every row, to see if only one column changed. Suppose in row 100 an email address in File A is [email protected], but it in File B, the data in that row is the same except that the email address is [email protected]. The sync process cannot tell which file was updated, and which email address is the one to keep.
• If the process finds a row in File A that doesn't match a row in File B, it cannot tell whether the data in that row was updated in one of the files, or whether a new row was added to each file.

This just scratches the surface. There are other problems that get stickier.

With structured data, it is a bit easier to identify and resolve conflicts, especially if changes are timestamped.

[ReneNeyrey]

I came up with this idea using excel, before you told me About intermedia

1. rename the existing notebook as Info Master
2. Copy the block of all cells in Master that have data in them A a few blank columns to the right and a few rows at the bottom for future use.
3. open a new blank excel spreadsheet and paste what I copied as a link.
4. Name that Notebook Info Assistant. Make this a read only sheet to avoid accidently making changes.
5. If a change needs to be made, open the Info Master sheet, make the change, save, and close.
6. Save and close the Info Assistant workbook.
7. Right click the Info Assistant file, select copy, and paste.
8. Rename the new file Info Boss.
9. I adopt the same practice, if a change needs to be made do it on info master, save, and close.

Sorry, I'm overly wordy. We tried this out using a dummy spreadsheet, And it seems to work fine. But if intermedia will deliver on their promise of "Co-editing in real-time" that would be optimal.


Thank You,