Help - Auto update from multiple spreadsheets

[Guest]

Problem: I have multiple tabs in one spreadsheet that get updated by many
different people.
Current methodology: I send the spreadsheet out to people, they each update
their tabs and send it back to me. I then open each spreadsheet that is
returned, locate the correct tab on the master spreadsheet and paste the
updated information in.

I am looking for a way that automates much of this updating process. Here
are my thoughts, but I am not sure if this is possible. Also, if there are
other solutions, I would love to hear about them.

I was thinking if I had one separate file for each tab, I could have a tab
for each in a master spreadsheet that I could link to the information in the
individual files. If the individual file was updated, the tab in the master
would automatically be updated with the new information. Notes- I want the
actual information in the tab as opposed to just having links to each file.
In MS Project this can be done through sub projects.
Thanks.

 

[Guest]

Can all the usrers access the file e.g. is it in a directory where they can
all access it? If so then select: Tools > Share Workbook. Then click the
radio button on the Editing tab "Allow changes by more ......". Everybody
that accesses this file can update it automatically. They can even have the
file open at the same time.

HTH

Ian

 

[Guest]

Hi Ina.
That sounds like a great way to do this, I will have to play around with it.
When separate people have the file open and are updating different tabs, and
then save the file will they get the prompt about conflicts or only if they
are editing the same tab? My concern- we have some people who aren't
experienced computer users.

Has your experience with this functionality been positive (I guess so, you
probably wouldn't have recommended it otherwise). What are some of the risks
with using a shared workbook?

 

[Guest]

Reading the Excel help I see that the conflict only occurs if two users make
a change to the same cell. Very smart.

 

[Guest]

The main problem associated with this functionality is that once you have
shared the workbook then a number of options associated with formatting are
removed e.g. merging cells. If you want to make structural changes then you
have to untick the shared option which means people temoprarily may not be
able to access the workbook. For inexperienced users this may be a problem.
My advice is to build the workbook, make sure you are happy with it and then
share it - don't share too soon. The only other slight downside is that
inexperienced users may not be quite certain about the questions/prompts they
receive when other people edit the workbook.

Apart from this I haven't had any issues using the shared functionality and
it saves a lot of messing about with copy/paste.

Ian

 

[Guest]

Thanks so much Ian. I started to play around with it and I think this may be
the way to go. A coworker and I are going to test it out on Monday.

Do you know if there is a way to show "last modified day" on each individual
tab for that individual tab? Right now it is a field that people have to
manual update. They usually forget to and we either ignore the field or I
update it.


P.S. It really was 4:30am my time when I replied, which is why I spelled
your name wrong. Sorry!