• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automatically updating one spreadsheet from another

S

seansr

Member
I have 1 x Master spreadsheet that collates all information, and 4 identical spreadsheets that 4 single people work on.

Each of the 4 people are responsible for one section of the spreadsheet, say 10 columns each. but all of the information then needs to updated on the master spreadsheet.

The 4 seperate spreadsheets may all be worked on at the same time.


Once all the 4 individual spreadsheets are compleated they copy and paste their relevant comulmns into the master. Then take a copy of the full master and they are ready to work on again.


Information entered by another person may be required by someone else hence they take a copy.


Can anyone suguest a way of updating the master spreadsheet from the individuals, and then refreshing the indiviadual to match the master.


If this could be automated it would be great, my boss wants it to happen on the hour, however if a macro could do this I would be most gratefull.


The individual and master spreadsheets are all in the same directory on a server, which they all remote into, to work.


Presently there are 75 rows but this will grow to nearer 2000 in the fullness of time and presently there are column A - DW.


I am fairly ok with Macros, once I understand what I am doing, but not so happy with VB, but haqppy to try anything.

Any help would be most grateful

Seansr
 
Hi Seansr ,


Can you clarify the following points ?


1. The individual workbooks have only entered data in their 10 columns ; in case there are any formulae in these 10 columns , then only the result values of these formulae will be copied to the master workbook. Since you say that the full master workbook is to be copied and pasted back into the 4 individual workbooks , whatever formulae were present in the individual workbooks will all be overwritten.


2. Since the data entry in the individual workbooks may or may not be at the same time , and the transfer to the master workbooks may take place at different times , copying the master workbook back to the individual workbooks should be done only when all the 4 persons have updated the master workbook.


3. Each of the individual workbooks should have a SUBMIT / DATA TRANSFER button so that clicking this initiates the data transfer , and puts a tag or increments a counter in the master workbook ; when tags or the counter register that all the 4 updates have been completed , then the master workbook can mark a second tag ; when the individual workbooks initiate a master workbook copy , this tag will have to be checked to ensure that a fully updated master is copied back to the individual workbooks.


Any other comments ?


Narayan
 
Narayan,


Thanks for your interest.


1 There are no formula in the spreadsheets is is purley data caption. There is a link to another spreadsheet.

Each person who own one of the 4 individual s/s will be responsible for updating their columns, and will not touch the other columns, but may be reliant on the data in the other columns


2 ok


3 like it,but not sure it will work, as one of the 4 spreadsheets may only be updated a couple of times a week, where as some of the others may be updated every hour.


I am happy if when they submit they then pull back a copy after that so theres is updated with their input and all rest at the same time


Many thanks


Sean
 
Hi Sean ,


Can you give full details of the workbooks names , the tab names , the column names and so on ?


Or , can you upload the workbooks ( 5 of them ) after removing all the data , and specify the columns that each person will be working on ?


Narayan
 
Narayan


Please find the file in dropbox


http://dl.dropbox.com/u/66314380/Project%20Tracker%20columns%20sorted.xls


I have put some instructions in the instructions tab


Many thanks


Sean
 
Hi Sean ,


Sorry for the delay , but I'm having problems ; can you let me know the version of Excel you are using i.e. are your workbooks Excel 2007 or later , or are they Excel 2003 or earlier ?


Narayan
 
Narayan


Sorry for my delay in getting back to you. The version on our servers is 2003


Hope that's helpful?


Sean
 
Hi Sean ,


Is it possible we can correspond by email , since I anticipate more dialogue before this is over.


My email is narayank1026[at]gmail[dot]com


Drop me an email so that I can send you the partial solution with a few doubts.


Narayan
 
Hi Sean & Narayan,


Actually I was looking for the same resolution..

Our all agents use individual worksheets to fill up the completed work. By end of the day we open all the 10-12 files and copy all the data to the master sheet, I think, Sean & my problem is same so once I will get the idea, i can do it. But off-course I need your help for that.

so can I please join you?


Atul

:)
 
You must log in or register to reply here.
Back
Top