Solved Combining information from one master and 11 user workbooks

Microsoft Excel 010 - complete package
April 30, 2013 at 12:01:09
Specs: Windows XP
Ideally I'd love vba script for this and am working on that option but need some assistance. I have two people that log in new cases to a workbook but 11 users that need to see the data and then assign themselves to the individual cases. Currently they just have the one workbook that keeps locking up and erasing data.

The wb is set up with five columns. 1) Customer Name 2) Customer Number 3) Date Received. (These three columns are input by the two loggers.) 4) Analyst Name 5) Date Taken (These two are inoput by one of the 11 users.)

Everyone needs to see all 5 of these fields as soon as they are enterted. I'd like to keep the one master that will be used to log the cases and give each user an individual wb that's linked to the master so everyone sees everything. Does this sound doable to anyone?

See More: Combining information from one master and 11 user workbooks

April 30, 2013 at 19:01:47
✔ Best Answer
The first thing I would want to know is why the workbook is "locking up and erasing data". As I'm sure you know, that is not normal.

Is the workbook that everyone uses a shared workbook that allows multiple users to edit it at the same time?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

May 1, 2013 at 04:52:53
It locks up because multiple users are trying to access it at the same time. Sometimes the message that comes back is not the usual another user is in this workbook, do you want to be notified or read-only. Sometimes it says this workbook is locked by another user. Then, because there are so many users, no one can access the workbook to make their updates and changes.

It's shared, so if there are two people editing it and one of them gets the error that states another user has made changes, they don't always realize they're about to override someone else's data. We can call this user error but it doesn't take away from the problem of lost data.

But these issues aren't normal? I'm surprised you said that because both seem to be well known problems with Excel, I've seen tons of problems reported, especially with the locking up issue. I don't have access to servers to make changes and there are about 40 people total who this effects so while I can't resolve this problem, I am looking for a work-around.

Any ideas?

Report •

May 1, 2013 at 07:37:49

As far as I know, there is no easy way around the workbook locking up when another user is adding or editing data. You could probably have multiple spreadsheets with one master sheet that has VBA that grabs the data from all of the other workbooks, but that just sounds like a convoluted mess...

This example you gave us is a prime candidate for an Access Database. You would create a Front-End database that just has a simple entry form that is on everyone’s computer that needs to enter information, then you would create a Back-End database that is linked to the Front-End that stores the data.

The beauty of an Access Database is it can handle all of your connections at once and only locks the record that is being worked on and not the whole database...

Do you have any experience using Access? The cool thing is that this task is simple enough that it may not even require Access VBA. You may be able to create all of your forms and tables using wizards.

Law if Logical Argument: Anything is possible if you don't know what you're talking about.

Report •

Related Solutions

May 1, 2013 at 07:40:32
Thanks, Newbie, but Access is not an option.

Report •

May 1, 2013 at 07:49:25
Hmm... That's too bad...

Well, I will do some brainstorming for you to try and come up with a working Excel solution.

No promises....

Law if Logical Argument: Anything is possible if you don't know what you're talking about.

Report •

May 3, 2013 at 12:42:42

There are ways of doing this using multiple work books, but there are so many variables (where the wb's are located, what if the machine that hosts the main wb is off, how do we get the information somewhere that it is viewable from the main wb, what if two or more of the satellite wb's try to load data to the master wb at the same time, etc, etc...) that I'm even hesitant to spend as much time as it would take to get this mess working...

I know you said Access isn't an option, but you really really really should look into Access and learn more about it. I've created more Access 'programs' that do almost exactly what you're detailing here than I could ever tell you about. I've even got an access database that is configured in a front-end back-end configuration that has over 100 users entering data and about 10 different people monitoring the data entered and running reports all from the same back-end database... Even though I used VBA to handle most of the actions in my Access database, it still only took me a few days to create it. Like I mentioned above, yours is simple enough you could do 99% of it using wizards without even touching VBA.

Something to think about...

Sorry I couldn't help... :(

Law of Logical Argument: Anything is possible if you don't know what you're talking about.

Report •

May 3, 2013 at 12:56:26
Thanks, Newbie. After talking to management (who still said No to Access) I approached SENIOR management who thought that sounded like a good idea after all! Now I'm about to start building my first Access database and will be incorporating the data fed from a rather large Excel spreadsheet! I am very excited about the prospect and will probably be posting on the Access forum here in a few days, needing help.

Thanks again!

Report •

May 3, 2013 at 13:41:37
No problem!! I'm glad you're able to look into Access!!

I will keep an eye out and help you as much as I can!

Just a little advice to get you started:

If you will have multiple people entering data at any given time, you will need to separate the tables containing the data from the user interface by creating 2 separate access databases. One Access Database that has a form that will be used for data entry and a second Access Database that will contain the tables that will contain your data. Then you will link the two databases together so you can enter data in the form and it places it in the second database.

The database with the data will need to be on a server or computer that is accessible by all parties entering data.

After you have your forms linked to your second database and working the way you want, you can give a copy of the database with the forms to each user and everyone will be adding data to one location!

After you get some data collected, you can create another front end that contains only reports and link it to the original Access Database that contains your data and give this front end database to management so when they open it, they just have buttons that open the reports they want to see.

Things to Google that will help you:
Access Reports
Access Forms
Linking Databases
Access Macros

Law of Logical Argument: Anything is possible if you don't know what you're talking about.

Report •

May 6, 2013 at 04:57:28
Thanks for all your help, Newbie. I will absolutely be looking for you when I post about Access!

Report •

Ask Question