Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.




1952 posts

Uber Geek

Lifetime subscriber

#233676 27-Apr-2018 10:14
Send private message

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.


Create new topic
4797 posts

Uber Geek


  #2003367 27-Apr-2018 10:23
Send private message

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,

 

 

 

 


2523 posts

Uber Geek

Lifetime subscriber

  #2003370 27-Apr-2018 10:26
Send private message

... 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."

 

 

 

 

 


 
 
 
 


2175 posts

Uber Geek

Lifetime subscriber

  #2003417 27-Apr-2018 11:34
Send private message

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.

 

 

 




1952 posts

Uber Geek

Lifetime subscriber

  #2003456 27-Apr-2018 12:44
Send private message

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.


2175 posts

Uber Geek

Lifetime subscriber

  #2003468 27-Apr-2018 13:10
Send private message

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.




1952 posts

Uber Geek

Lifetime subscriber

  #2003483 27-Apr-2018 13:41
Send private message

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.


What does this tag do
1026 posts

Uber Geek

Subscriber

  #2003484 27-Apr-2018 13:43
Send private message

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

 

 

 

 


 
 
 
 


2523 posts

Uber Geek

Lifetime subscriber

  #2003485 27-Apr-2018 13:44
Send private message

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.




1952 posts

Uber Geek

Lifetime subscriber

  #2003489 27-Apr-2018 13:52
Send private message

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.


970 posts

Ultimate Geek

Trusted

  #2003516 27-Apr-2018 13:56
Send private message

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.


970 posts

Ultimate Geek

Trusted

  #2003518 27-Apr-2018 14:00
Send private message

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.




1952 posts

Uber Geek

Lifetime subscriber

  #2003527 27-Apr-2018 14:16
Send private message

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





Life is too short to remove USB safely.


4691 posts

Uber Geek

Trusted
Subscriber

  #2003593 27-Apr-2018 15:15
Send private message

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.


Create new topic




News »

Pre-orders for Huawei MateBook 13 open now
Posted 14-Aug-2020 14:26


Freeview On Demand app launches on Sony Android TVs
Posted 6-Aug-2020 13:35


UFB hits more than one million connections
Posted 6-Aug-2020 09:42


D-Link A/NZ extends COVR Wi-Fi EasyMesh System series with new three-pack
Posted 4-Aug-2020 15:01


New Zealand software Rfider tracks coffee from Colombia all the way to New Zealand businesses
Posted 3-Aug-2020 10:35


Logitech G launches Pro X Wireless gaming headset
Posted 3-Aug-2020 10:21


Sony Alpha 7S III provides supreme imaging performance
Posted 3-Aug-2020 10:11


Sony introduces first CFexpress Type A memory card
Posted 3-Aug-2020 10:05


Marsello acquires Goody consolidating online and in-store marketing position
Posted 30-Jul-2020 16:26


Fonterra first major customer for Microsoft's New Zealand datacentre
Posted 30-Jul-2020 08:07


Everything we learnt at the IBM Cloud Forum 2020
Posted 29-Jul-2020 14:45


Dropbox launches native HelloSign workflow and data residency in Australia
Posted 29-Jul-2020 12:48


Spark launches 5G in Palmerston North
Posted 29-Jul-2020 09:50


Lenovo brings speed and smarter features to new 5G mobile gaming phone
Posted 28-Jul-2020 22:00


Withings raises $60 million to enable bridge between patients and healthcare
Posted 28-Jul-2020 21:51



Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.


Support Geekzone »

Our community of supporters help make Geekzone possible. Click the button below to join them.

Support Geezone on PressPatron



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.