Multiple users for excel workbook

nachiketdp

Board Regular
Joined
Jan 31, 2007
Messages
53
Dear All,

I am using Excel 2010 in my office. I have developed an excel file for maintaining data related to sale of inventory and receipt of moneys from debtors. I want multiple users in my office to work upon it concurrently. However, this is not happening very smoothly. I tried sharing the file on dropbox but it is generating conflicted files. I have got many macros, userforms and pivot tables embedded in my file so I can't 'share' the workbook.

Can someone suggest me a solution whereby my excel file becomes like a software accessible to all simultaneously? Your responses will be truly appreciated as this issue has been gnawing me since several days!

Thanks in advance,
Nachiket Pendharkar
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
I have done that in a large way. Of course, my main purpose was take new inputs and save them into an external excel file. The main template guided users to input data, then when they were finished, it saved all data into one database file. The Template was set as read only so they couldn't make permanent changes. Everybody that needed to use it had access to the server folder. Every time the template was opened, it imported the main database to give them fresh data. I also allowed them to import the main DB during their session.

In your case, with out of network users, I don't see that being practical.

Other methods are to have satellite workbooks. Whenever a user sends you their workbook, the master imports their new entries and incorporates them into the central database. You would have to supply new versions after all the satellite workbooks were updated for the day. This version is actually easy to maintain. The only drawback is that users cannot change records that others are also maintaining. Your team would have to have designated records that others didn't touch.

Lots of work.

Sharing workbooks DOES NOT WORK. Excel got that part wrong.

Jeff
 
Upvote 0
Thank you very much, Mr Mahoney! Yes, I was also thinking along the same lines as I did lot of googling and have come to the conclusion that I cannot have a single workbook holding the data as well as the interface for the users. So now I am thinking of maintaining several database workbooks, each containing their own data and every user will have an interface file. The database files will be shared workbooks. The interface files will keep communicating with the database files and will input/retrieve data from them. I will use a dropbox location as the common location to host the database files while interface files may be located anywhere on the local drive.

What's your opinion on this?
 
Upvote 0
Using VBA to retrieve data from satellite workbooks is better than formula linking.

You can open a workbook as READ ONLY and get the contents faster than opening a workbook as a shared workbook and reading live data. This works if the data is only going one direction > into the Master. It works best if you overwrite your local files from dropbox, keeping the same name as before. Build a table with a list of satellite workbook names.
Code:
Set TSWB = Workbooks.Open(PathFile, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)

If you have bi-directional data, meaning, the satellite workbooks need to have a copy of the full database, then you will have to schedule a period of time each day where the satellite workbooks are not being updated. You run your updates and send the updated files back to dropbox. That means you'll have to update all satellite workbooks after you pull all the data from them.

With that, let me know how I can help. I have to work full time also. Post some code and we'll get you started.

Jeff
 
Upvote 0
Sorry for the late reply, I was tied up in some year ending processes (In India, our financial year ends on 31st March). I appreciate all your inputs, I am now developing the work as discussed earlier on this thread. I will stay in touch with you regarding the progress.

Thank You! :)
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top