Shared workbook excel 2011 mac
The following methods may help you to rescue data that might otherwise be lost. If you are editing a worksheet and the workbook becomes corrupted before you save your changes, you can recover the original worksheet by reverting it to the last saved version. To revert workbook to the last saved version, do the following things:. Note: this step will revert the workbook to the last saved version of the workbook. Any changes that may have caused the workbook to get corrupted have been discarded.
Save the workbook in the SYLK format, you may need to filter the corrupt elements. SYLK format is mainly used to remove printer corruption. Excel isn't a multi-user platform. Maybe it will be in the future, but it certainly isn't now. If you need to share data, use a separate database to house the data. Debra left off charting from her list of shared workbook incapabilities.
If I can't do charts and pivot tables, then shared workbooks are less than useless. Our clients have been using both types of sharing single book vs multiple book for months, without a hitch. Re the loss of the ability to do charts and pivot tables, why are you using a shared workbook to show pivots and charts? We use shared books for data-collection ONLY, which is their purpose.
We keep it simple-- no macros. Thanks Jan Karel, I didn't realize that shared workbooks could also get corrupted so easily. That should convince everyone to avoid them. And Jon, how could I have omitted charts? Thanks for adding it to the list of things you can't do in a Shared Workbook.
It would probably be quicker to list the few things that you ARE able to do. If you are going to list the things you ARE able to do with a shared workbook then lets just call it Microsoft Word with Tables. We have used shared workbooks for many years now, and live with the problems described. But so far I have not found an affordable alternative. We use it to share details, in real time, of our production system.
We do some calculations within the workbook, for resource loadings for instance, so a spreadsheet is ideal. It also needs to be simple for people to use who are not very computer literate - "click here" kind of instructions. I understand a database is the "proper" solution, but it would cost a lot more than a basic license for MS office, which most machines at work have already. Used by about 10 people at the same time.
OOo is nearly there now, but not quite as good yet. If you know of any other solutions I too would be pleased to hear about them. Roger: A database "only" costs you the development time, your systems do not need an Access installation to be able to use it, just the Free Access viewer. I'd still urge you to consider porting to Access. Excel is probably the most commonly known tool in the Office Suite for managing data. Best tool? There's a long debate. From the standpoint of collecting information from various personnel throughout a company, Excel is the application everyone "knows how to use".
Low learning curve means lower cost. The add-in called Distributed Spreadsheet has helped a number of companies collect data from various parts of companies and then export the data to other systems. In this way, the reliance of workbook integrity is not an issue and the deployment costs are minmized.
Sometimes the answer lies in combining tools rather than the same tool used from beginning to end. I agree that database is the best solution. When you want multiple users to interact with an excel file - use it in a "Database" mode. Create a Reporting application which can pull data from the database. Excel has a very interesting addin called Template wizard with Data tracking which allowed users to add data to a database from an excel template. Instead of improving it MS decided to discontiue it from XP onwords At my office, we did the shared workbook thing a lot.
It was quick and dirty, and we didn't need the advanced features. Just a few people working remotely who needed to edit a few cells on a common workbook. We did end up with a few corrupted workbooks, but fortunately there were nightly backups, so the data loss was minimal. But the sad part is the rationale behind it all.
They didn't want to buy more Access licenses getting IT to do installations is a rude welcome to the bureaucracy , and paying someone to write the code that would write the information to a database or create anything more than a rudimentary solution was out of the question. All I got were blank stares when I suggested alternatives to shared workbooks. Sam, I remember that Template wizard, and lots of people found it useful, so I don't know why it was dropped from later versions.
JP, at least you your office wasn't expecting miracles from the shared workbooks, but it's pretty bad if even a very simple workbook gets corrupted when shared. I did some contract work for a major car manufacturer a couple of months back where they used a Excel to maintain a long list of parts.
100 thoughts on “Avoiding Shared Workbooks in Excel”
They decided to share this workbook, but within a week it was a mess. Well the multiple users were not only updating information row by row but also inserting and deleting rows, so when one user saved changed the whole structure of the document that everyone else was using was overwritten. I left about a month later and the team was still working on a solution for a problem that could have been solved in Access in 15mins.
Thanks Giff, that's a pretty sad story. If they can't or won't use Access, they'd be better off having one person do all the updates, and lock it down for everyone else. Can comments be edited if you share a workbook in excel ?
Mark, you can't edit existing comment text, but you can add to the end of a comment, if the sheet is unprotected, or if it's protected with users allowed to edit objects. Or, you could use a macro to remove the existing comment and create a new comment with the same text.
Then, edit the new comment before you save the workbook. Debbi, I don't have any sample code for that. If you post your question in the Excel programming newsgroup, someone may be able to help. You can access the newsgroups through the Microsoft web site: Excel Programming Newsgroup. If you just want data, how about everybody having the same workbook on their hard drive and writing to a text file on a shared drive?
It might be useful for something like simple HR records, perhaps a timesheet or a survey. Hi Will Office Service Pack 2 offer any improvements on shared workbooks? On the subject of shared workbooks If a car manufacturer sold a car with A? C but it did not work, would it be allowed to sell the car Its suprising Microsoft bundle shared workbooks but then advise dont use it, maybe they should put a 12 user limit on shared workbooks then on the 13th person deny access and prompt for MsAccess or SQL?
Gary - Microsoft does not advise not to use shared workbooks. We are independent users who have been burned by shared workbooks. You should also remember that SP2, like all service packs, corrects small but very visible issues that have not been around for umpteen versions of a product. Shared workbooks have been around for a long time, and the problems are not very visible unless you have actually tried the feature.
I've got a shared workbook that people at my company are updating constantly all day long. If I move this information to Access, can users update info in real time? Jaime, almost anything would be a better solution than using a shared workbook. You could post a question in an Access newsgroup, describing what you'd like to do with your data.
Someone there may be able to help you decide if Access is the best solution. You can access the Microsoft newsgroups through the Microsoft web site. Wow, This is not what I want to hear! I just spent some months working on a shared workbook application and am introducing it to the rest of our company. I've used shared workbooks for years now. My work around to the corruption problem is to backup daily before using it and then again whenever you run a potentially destructive macro or do any heavy data manipulation.
So far I haven't had any "unexpected" corruption issues in some years now. What drives me to the shared approach is the need for users to copy the shared workbook template and use it for their own projects. Projects are different enough that users need flexibility in how they use the tool. Users aren't very good at figuring out exactly what they need the "database" to do till they need to do it. The spreadsheet allows them to do things manually that may not be automated yet. Typically the spreadhseet is unshared when reports, extra sheets, and pivot tables are needed.
It may be that the unsharing cleans up the spreadsheet as well as far as corruption goes I just spent 2 months in creating a excel - vba application I am starting to have some issues when i share my workbook When you set a workbook to be shared, Excel creates a hidden worksheet that contains all the changes, who made them and when.
This file can grow rather large and do so quickly. Yo may notice this happening if you use the "Share" feature a lot. File size can create its own problems. If you opena shared file and no longer need to have the file in share move, I highly recommend turning off the share mode on the file. Hit my domain for more info. Is there anything i can do to avoid as much as possible the clashes in my workbook?
Is there any workaround on this shared excel scenario? Can Access solve the problem of data entering? I agree with previous comments about the fragility of this type of spreadsheet application. Otherwise it's great fun if you are an Excel freak! As for reports, pivot tables etc. Please don't think I am suggesting that everyone start trying to use shared workbooks - they are hard work.
I would recommend alternatives wherever possible, but it IS possible to get a reasonable result if you have to. Oh - another item to watch out for are "ghost sessions". Excel has a nasty habit of retaining data of sessions which terminated unexpectedly, such as when your server or network has a problem. These ghost sessions can become quite numerous at times of network problems - I have counted 26 of them on an application.
Like I said, best to use some other database application if you possibly can. Thanks Chris, your comments should help those who need to share a workbook, and want to avoid as many problems as possible. I have a single shared workbook that has crashed and become corrupted sporadically, and it typically happens more to remote users sharing workbook over WAN than local.
Questions: 1 Chris' comment said to avoid using a "Common data input area". What does this mean? Different worksheets? Distinct ranges within a worksheet?
How to Recover Any Unsaved or Overwritten Microsoft Excel Files
Our users don't enter into the same cells, but they do enter on the same worksheet. Do linked workbooks have any stability issues similar to shared workbooks? What other disadvantages would this cause? I'm sure those "ghost" sessions I mentioned increase the save time frame for each user. Short of writing some code to control each users saves, I have resorted to having my users ensure that they do not save their work at the same time. Not very neat, but I don't get many crashes since I instituted that regime.
Harder to implement if you have remote users of course.
- Popular Topics.
- mac browse windows network share?
- macos - Excel Opens Spreadsheet As Read-Only - Ask Different.
- Question Info;
- change language in mac word.
- my mac internet is running slow how do i fix it.
If you are using the master file continually, with arrays, you would have to regularly use the UPDATE LINKS feature each time you wanted to see current data again that is dependant on how often users save their files , but I guess that is no more work than saving a shared workbook master file in order to force updates from the user files. The users of course would not be able to see the full application data if the master file is on exclusive use setting.
If they don't, then linked workbooks may be the better option. I think we have to accept that Excel is not the best medium to use for multi users data input etc. I don't mind. Chris, Thanks for the thorough answer. Your comments match my experience. Our corruption occurs when multiple users are actively using the workbook. Unfortunately it is a deadline driven application where all of the users 3 people have 1 day each month to enter and save their data. The long term solution is to enter the data in our corporate forecasting tool, so there is light at the end of the tunnel, but some additional development work needs to occur to get there.
I'll investigate linked workbooks in the meantime. If there is no data input in the master workbook, it may make sense to share that one and have the individual workbooks in exclusive mode since they wouldn't need to save it. That brings to mind another idea - setting up the Master workbook as a template.
Not sure what downsides that could have having multiple open workbooks referencing the same input workbooks via links , but I may try it out and post my findings. Regarding setting up a linked-file master workbook on a shared basis, might it be better to set it up as exclusive-use and Read-Only with a password protection.
How To… Recover Word & Excel Files After A Mac Crash
That way if anything goes awry with the read only file the original is left intact. I have a client using shared workbooks wondering why sometimes the person in the workbook shows up as "System Adminstrator" rather than the actual name of the person. This presents a problem when they want to contact the actual person in the workbook. It seems to happen more with files than files. Does anyone know the answer.
Ed, firstly, I'm not a System Administrator, but as I understand it, when Excel is originally installed on a server I believe the server automatically enters, as default, the user name of the person doing the installation - usually someone logged in as "System Administrator". This name will stay like that for all susbsequent users of Excel until those individual users change this ID. This can be changed to the users name. If you are using Excel then the user maybe hasn't done this yet.
On shared workbooks this is the name Excel displays for those users with the file open. I want to commend people like you who take the time to post such excellent instructions for people like me. You hard work does not go unappreciated! Alma Grace. I agree. These instructions were clear and complete. You've provided excellent instructions. Thanks for taking the time and effort to share them. Greatly appreciated. Thank you, just want I needed for the Mac, kept getting PC instructions You would think Microsoft would just make this a one click option I seem to get an error message onc ei enter this function any insights?
I'm really grateful for this lesson. Thanks so much for taking the time to explain this process. Hi Cru. Thanks for pointing that out. I'll update the lesson to include both options. Much clearer than the Microsoft help! I've been looking for an easy way to do this for years! Thank you so much for the simple and clear instructions - MUCH appreciated!
This helped me out a lot! Thank you so much!