Collaborative Spreadsheeting : pros, cons, any tips?

Forums › IT Pro and developers › Collaborative Spreadsheeting : pros, cons, any tips?

kiwifidget

1952 posts

Uber Geek

Lifetime subscriber

#233676 27-Apr-2018 10:14

Hi everyone,

I find myself in the position of managing a spreadsheet for a group of people.

They email me their updates etc and then I email them back the updated spreadsheet.

And its starting to get confusing.

I am now wondering if having the spreadsheet available to all of them is a better way to go.

I've never set this sort of thing up before, but I'm sure there is good and bad in it.

I know of google docs, is that the easiest?

I'm thinking I can go either of 2 ways.

1. they keep emailing me their changes and I update the spreadsheet which they can see but not edit.

2. I give them all edit rights and hope they don't stuff it up.

Would each person need a google account for this to work?

Any pros, cons, and tips you have to offer would be gratefully appreciated.

Please, simplicity is essential. Some of the people are not too tech savvy, even less than me!

Thanks.

Life is too short to remove USB safely.

wellygary

4797 posts

Uber Geek

#2003367 27-Apr-2018 10:23

If you go down the path of allowing them all edit rights, you need to ensure you have a system of copying the spreadsheet each night to a daily backup file that they cannot work on,

This allows you to roll back to if something untoward happens,

kryptonjohn

2523 posts

Uber Geek

Lifetime subscriber

#2003370 27-Apr-2018 10:26

... or even better would be a system with version control so you can roll back to any previous version in case they do make a catastrophic cock-up of it.

Google sheets has this, apparently:

"Never hit “save” again

All your changes are automatically saved as you type. You can even use revision history to see old versions of the same spreadsheet, sorted by date and who made the change."

Hammerer

2175 posts

Uber Geek

Lifetime subscriber

#2003417 27-Apr-2018 11:34

I would avoid creating dependencies that you cannot control because it is very easy to break a working spreadsheet. Each person who can edit it is another person who can inadvertently create problems for you.

Are the edits of spreadsheet logic or just data or both?

Wherever possible, I would separate the data entry from the formulae: the editing of data should be a simpler exercise and easy to audit; the collaborative editing of logic is much more fraught.

How large and complex is the spreadsheet?

Consider separating one large spreadsheet into several smaller files that makes them more easily managed.
Alternatively, group each persons data entry into a contiguous block which can be easily copied into email. They could then edit the data in an email and return it to you so you can copy it into the correct place in one easy action.
Consider separating out reporting into separate sheets. Many spreadsheets are overly complicated because they attempt to combine calculation with output presentation. It is often better to separate them.

What is the cost of errors? The higher the cost then the more careful you should be by seriously considering any suggestions to improve change management. The cost to identify and fix problems is generally many times higher than the cost of your current inefficiency. I worked for a bank which lost millions of dollars of business because one change to a spreadsheet invalidated the overall result - it wasn't picked up until a lot of business was lost. I don't know if they ever found out who made the change.

Consider adding check calculations that will highlight if the formulae logic is changed.

kiwifidget

1952 posts

Uber Geek

Lifetime subscriber

#2003456 27-Apr-2018 12:44

The spreadsheet is just a large table, 800 rows, 14 columns.

It is all data, no formulas, bar one column I am trying to set up hyperlinks in.

The goal is to fill in all the cells.

I can periodically save the file locally to avoid major cock-ups.

Life is too short to remove USB safely.

Hammerer

2175 posts

Uber Geek

Lifetime subscriber

#2003468 27-Apr-2018 13:10

It is an unusual spreadsheet that has no formulae. But being data only does mean that you might just as easy use a word processor or any other program that handles tables and works well with collaborative editing.

Another thought is that if you have the coordinates in the table then you can create a table from a list of data. It would work a lot like the opposite of an edit log. For example, if you have a table with columns for each product and rows for daily sales then you can assemble the table automatically by processing a list of three values "Product", "Date" and "Sales". Inputting the data in this way might reduce the problem of errors because the latest changes will be visible at the end of the list.

kiwifidget

1952 posts

Uber Geek

Lifetime subscriber

#2003483 27-Apr-2018 13:41

Cheers @hammerer , we went with a spreadsheet as we need to use the COUNT function and sort/filter the data ( i love pivot tables).

That seemed more spreadsheetery than anything else.

Life is too short to remove USB safely.

jnimmo

What does this tag do
1026 posts

Uber Geek

Subscriber

#2003484 27-Apr-2018 13:43

Google Sheets works well - otherwise consider trying out something like Airtable if you might want to potentially attach other data back to this table. It is similar to Google Docs but you could for example only have the columns that you want others to contribute to editable

kryptonjohn

2523 posts

Uber Geek

Lifetime subscriber

#2003485 27-Apr-2018 13:44

I really think you should just put it on Google Sheets and when you want a milestone stamp a named version in the version history. All the other versions are also saved and it is able to do all the usual spreadsheety stuff including forms, macros, lookup validations, protected areas etc, and you can import/export Excel.

kiwifidget

1952 posts

Uber Geek

Lifetime subscriber

#2003489 27-Apr-2018 13:52

Yep, I've gone down the Google Sheets route, though thanks for the other suggestions.

I am having an issue with linking to pdf files however, but its not a major.

I have saved some pdfs in the same google drive as the spreadsheet, but not the same folder, dont know if that matters much.

I created a hyperlink in the cell using a shared link for the relevant pdf.

Instead of just being able to click the link label, I am having to hover on the link label and then click the pop-up box.

Is there a way to just click the Link label in the cell and have the pdf open straight away, rather than this 1 1/2 step approach?

Life is too short to remove USB safely.

amanzi

970 posts

Ultimate Geek

Trusted

#2003516 27-Apr-2018 13:56

Both Google Sheets and Excel Online will give you the collaboration features you need and both are free. Both have version control and both support pivot tables.

Try them both and see which works best for you.

amanzi

970 posts

Ultimate Geek

Trusted

#2003518 27-Apr-2018 14:00

kiwifidget:

Is there a way to just click the Link label in the cell and have the pdf open straight away, rather than this 1 1/2 step approach?

I just tried that in both Google Sheets and Excel Online. Excel Online lets you click on the label and go straight to the linked document, but Google Sheets just offers the little popup.

kiwifidget

1952 posts

Uber Geek

Lifetime subscriber

#2003527 27-Apr-2018 14:16

hi @amanzi , thanks for that update. I'll stop googling it!

Life is too short to remove USB safely.

alasta

4691 posts

Uber Geek

Trusted

Subscriber

#2003593 27-Apr-2018 15:15

In my experience this can be an absolute minefield, particularly when your end users are computer novices. You end up with people deleting rows or columns, entering data into the wrong fields, entering text into fields that should have a date, pasting data across from other files with disparate formatting, etc.

Excel has data validation features which should mitigate these problems, however in practice it is useless because you can disable the validation in a particular cell simply by pasting something into that cell. I'm not familiar with Google Docs, but hopefully it probably doesn't have such a mind-blowingly stupid design deficiency.

In an ideal world data gathering activities like this require a database with rigorous data validations designed into it. In a large organisation it can be hard to get the right people on board to help design something like that, but it will save you a huge amount of stress in the long run.