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.


kiwifidget

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

 

 





Delete cookies?! Are you insane?!


Create new topic
wellygary
8312 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,

 

 

 

 




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

 

 

 

 

 


Hammerer
2476 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.

 

 

 




kiwifidget

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

 

 

 

 





Delete cookies?! Are you insane?!


Hammerer
2476 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.


kiwifidget

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





Delete cookies?! Are you insane?!


jnimmo
1097 posts

Uber Geek


  #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

 

 

 

 


 
 
 

Cloud spending continues to surge globally, but most organisations haven’t made the changes necessary to maximise the value and cost-efficiency benefits of their cloud investments. Download the whitepaper From Overspend to Advantage now.
kryptonjohn
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.


kiwifidget

"Cookie"
3413 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?





Delete cookies?! Are you insane?!


amanzi
Amanzi
1292 posts

Uber Geek

ID Verified
Trusted
Lifetime subscriber

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


amanzi
Amanzi
1292 posts

Uber Geek

ID Verified
Trusted
Lifetime subscriber

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


kiwifidget

"Cookie"
3413 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!





Delete cookies?! Are you insane?!


alasta
6703 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 and reviews »

Air New Zealand Starts AI adoption with OpenAI
Posted 24-Jul-2025 16:00


eero Pro 7 Review
Posted 23-Jul-2025 12:07


BeeStation Plus Review
Posted 21-Jul-2025 14:21


eero Unveils New Wi-Fi 7 Products in New Zealand
Posted 21-Jul-2025 00:01


WiZ Introduces HDMI Sync Box and other Light Devices
Posted 20-Jul-2025 17:32


RedShield Enhances DDoS and Bot Attack Protection
Posted 20-Jul-2025 17:26


Seagate Ships 30TB Drives
Posted 17-Jul-2025 11:24


Oclean AirPump A10 Water Flosser Review
Posted 13-Jul-2025 11:05


Samsung Galaxy Z Fold7: Raising the Bar for Smartphones
Posted 10-Jul-2025 02:01


Samsung Galaxy Z Flip7 Brings New Edge-To-Edge FlexWindow
Posted 10-Jul-2025 02:01


Epson Launches New AM-C550Z WorkForce Enterprise printer
Posted 9-Jul-2025 18:22


Samsung Releases Smart Monitor M9
Posted 9-Jul-2025 17:46


Nearly Half of Older Kiwis Still Write their Passwords on Paper
Posted 9-Jul-2025 08:42


D-Link 4G+ Cat6 Wi-Fi 6 DWR-933M Mobile Hotspot Review
Posted 1-Jul-2025 11:34


Oppo A5 Series Launches With New Levels of Durability
Posted 30-Jun-2025 10:15









Geekzone Live »

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



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.