How can one spreadsheet be shared with a 150 employees without slowdown/crashing?

Hi everyone,

I've created a staff leave planner which needs to be shared amongst 150 employees. I've tried to share it, but it slows down to a point until it eventually crashes.

Do I need to create some sort of database to help manage the load?

Originally Posted by Neil_

Yes. It's at this point that you should seriously consider moving the data to Access. If you have that many people at the data at the same time, I would also split the database into a front end / back end set up. When you get to around 250 employees, or over a gig in data (Access max is 2 gig per back-end), its time to think about up-scaling further to a SQL Server solution.

I'm not sure how active the Access forum is on here, but the people over at Utter Access have been immensely helpful with me.

My apologies, but I didn't explain clearly. The spreadsheet is shared in a folder and upto 150 employees can access it, however truthfully the max to access it at the same time are 10 employees.

MS Access is not available, so is there anyway I can speed up the access further, such as splitting the data off or creating a short cut which only loads the basic features of Excel, thus improving performance?

We have a similar thing, you might see an improvement by breaking sheets off to other Workbooks, which individuals work on, having a master which has links to the other Worksheets.

The down side to this is that you will need to ensure that the subs Workbooks are regularly saved and that the Master is regularly updated (edit links, update values), to make sure it has updated info. Also the Master may be slower due to multiple links.

I do apologise guys, but could you give me a real excel example? How would it look and how would I be able to break it down?

@ Neil - The leave system is self-serve, so nothing on this leave diary needs to be authorised by a manager, so long as there is a space available.

@ llamafarmer - Does having separate sheets make massive improvements?

I don't understand how splitting the sheets make any difference to the performance? For example lets say an employee opens his/her leave spreadsheet with only their details on it, and books leave, but in order to book it, excel would then have to also open the worksheet which has all the calculations, so thats two worksheets now, hence slower performance???