Best way to automate combining of multiple workbook data on trigger?

[dylansmith]

There are a lot of data which I require from my team of 24 pax on a weekly basis. There are standard columns from A to T.



What happens now is that when 1 person goes in to update the data, he
- runs the risk of corrupting the master data
- locks the workbook and can't let other people input their data (we can't use office 2016/365 here) on the shared drive

I'm thinking to let each member have their own workbook with the same template, and run a macro (or using some other trick) to auto-combine their entries within a table across 24 workbooks.

Is that possible? Ideally, there should be no hardcoding but if not, happy to consider other options too.


**********************************************-

Separately, I have created a few Pivotcharts but it's weird that the default is I can only display in Quarters or Years, but not Months. Even with an additional Timeline bar, the charts will only display in Q or Y, but not M. What is wrong and what can I do to rectify the issue?

Thanks!

[Pecoflyer]

You will find inspiration at http://www.rondebruin.nl/win/addins/rdbmerge.htm
or
http://www.rondebruin.nl/win/section3.htm

It's always best to start one thread for each question you have, otherwise members could skip the second question after answering the first

[dylansmith]

thanks for sharing! unfortunately, I'm unable to use add-ons at my workplace.

For the VBA option, is there any way to detect changes to values within each workbook's rows, so they can be updated in the Master file accordingly? The current one simply adds whatever is available within individual workbooks.

Another implication is that if individuals make changes to their own workbooks, I won't be able to tell unless I read through each entry manually. Is there any checking mechanism I can put in place then?

[dylansmith]

any advice pls?

[Pecoflyer]

I moved the thread to the programming forum. See what the Gurus there can do