Thread: Update Main workbook from multiple user books

Hi All,

Based on the advice I received so far this is what I need to do.

Almost all experts were against the idea of a Shared workbook especially with multiple users.

So I want to allocate dedicated workbooks to around say 10 people and every 10 minutes or so I want data from their workbook to be updated in a standalone workbook.

So ideally the standalone workbook would have overall data of all the inputs the other users are making and at the same time is protected in its left because no one is using it directly.

Now the question is how do I create this?

How do I link user workbooks to the main/standalone work book so that data can be grabbed from the user workbook to the main work book without any conflict.

Ps: the user work book would have basic macros running in it.

Please help

Thanks

Saw one exaple in search but it was a bit too confusing .... I use Excel 2003 and my expertise is low in excel

Hi buddy,

Could you post an example?

You might not even need macros if you use data->import but that's hard to explain without seeing what you're doing and getting a bit more info

HTH

Not a simple issue. Sharing the workbook could potentially provide some options if you are sharing on some platform like Sharepoint (where you have certain lockout control). However that has it's own issues as well (as I gather others might have suggested).
I'm wondering if you'd be willing to use an alternative route - perhaps have all users post their data to a site (something as basic as an on-line survey like surveymonkey or the like). If that posted data remains public (and as long as you're the only one who know how to make sense of it), the task of drawing that data in through the use of a web-query in your main workbook is trivial... Though as a whole this is a bit of a patch-work approach.

Hi Gratis thanks for your valuble tips i'll deifintely try and look up that book you recommended.


Hi Cheeky, Nice to hear from you again

I have attached the example tracker below

Now this is what I plan to do; rather than share a workbook

I would put individual workbooks in a common drive acessible by all.

Each one of the 10 people would have their own tracker similar to the example and in the same common drive there would be a Main tracker with rows of same specifivation and details . maybe without macro since I dont think in the main workbook there is no need for macro.

Now maybe every 10 mins or so I want the data to be captured from these individual workbooks and these data should be consolidated in the Main Workbook.

How do I do that ?

Good choice on moving away from shared workbooks - they're such a pain if you need to change anything.

With your main tracker open, go to:
Data -> Import External Data -> Import Data
locate one of your files and choose what to import

You could do this for whichever of the sheets, from whichever workbooks you like. When you confirm the import, edit the properties of the query and you can see that you can set a refresh rate (10 minutes?)

Then you/we could write a really simply macro to compile the sheets together.

A slight improvement might also be to define dynamic named ranges in your separate sheets to help identify your imported data queries properly.

For a good example of a dynamic named range:

In your workbook you have defined TeamName:
='Code Sheet'!$A$6:$A$15
Try replacing that definition with
=OFFSET($A$6,0,0,COUNTA($A$6:$A$1000),1)

then see how the dynamic range size changes if you amend the list (add/remove members).


HTH

Thanks Cheeky let me try this out and then get back to you