how to protect master spreadsheet from corruption?

[Joe]

I have a master spreadsheet that I make multiple (10-50) changes to
cells per day. I sort and filter it every which way as well as cut and
paste from similar spreadsheets. I've been backing up the sheet on a
thumb drive and on a separate computer every night but I'm concerned
about corrupting the spreadsheet by user error then overwriting the
saved copies only to find out days later that I left one column out of
the sort or pasted from one sheet to another without copying all the
info.

The only way I can think to protect against this is to save maybe 5
separate days backups and overwriting the first day on the 6th day
etc. So if I do notice 2 or 3 days later that I made a mistake that
threw everything off by one column or row I can at least salvage the
master with the correct formatting while trying to piece together the
corrupt info from the day or two since.

My question: Is there a standard (easier) way of doing this or does
everyone just backup their master spreadsheet the best way they see
fit?

 

[JP]

Sounds like you're doing a lot more than most people, actually. There
are really very few ways to damage a spreadsheet, the only way it
happens for me is when the workbook is shared, and there are tons of
formulas. The file just keeps growing on its own until suddenly it's
unmanageable and must be rebuilt.

If you are copying the *entire file* to the thumb drive, why do you
think there will be a problem with the sort or only partially copying
the workbook?

HTH,
JP

 

[Joe]

what I'm worried about is making a careless error - like sorting a
column without highlighting the entire spreadsheet - then saving it
and not having a backup two days later when I find out what I did.

 

[MartinW]

Hi Joe,

You could just save it with todays date in the file name
using the format yy-mm-dd so that it will stack properly
in the directory, something like 08-04-12 My File.xls,
would be todays version, you can stack these files
in Folders with a similar naming procedure i,e.
04 April 2008
05 May 2008
etc.
These folders can then be stored under another folder
called simply 2008, 2007 etc.

That is the way I store my daily timesheets and dissection
sheets, I can call up a timesheet from 4 years ago in a couple
of seconds. Hard disk space is so plentiful and cheap these
days that there is no need to be miserly with it any more.
If it is an issue, due to a very large file size, then a regular
weekly or monthly cleanout is all that is required.

HTH
Martin

 

[JP]

We're all worried about that. I don't know what to suggest, Joe. Other
than some pretty complicated VBA event code, there's no way to really
protect your worksheet from user malfeasance (without causing major
inconvenience). What if someone deletes the entire network? The list
of possibilities is endless.

Work slowly and double check your work, and make regular backups,
that's what I do to keep careless mistakes to a minimum. If it fits
your needs, you might want to check out MS Access, my understanding is
that the security is much better than with Excel.


HTH,
JP