S
Steve Welch
I have a workbook (on a network drive) that is used to provide information
to our entire deparment via lookups from other workbooks (VLOOKUP())
I have this workbook password protected so that it won't be opened for write
unless the person is intending to do some updates in it.
We had a case the other day when one of our techs had opened this workbook
for write and then neglected to either save his work or close the workbook
at the end of the day - he simply locked his laptop and left it docked in
his cube.
The night shift had to go into the workbook and make a correction in it,
but couldn't because it was opened for write by this other individual;
Their solution (since the computer was locked) was to undock the laptop and
shut it down, freeing up the workbook.
Needless to say, he was a bit put out that he had apparently lost all of his
changes! (Hopefully he realizes whose fault this was!)
I am trying to find a solution to occurrances such as this, first I thought
of adding some VBA code that would simply timeout if no activity was
detected and then save and close the workbook - this is still an option.
The other option was to "Share" the workbook so that more than one person
would have write access to it at the same time - this would have prevented
the above incident.
The problem is that when I started testing with a clean workbook, it appears
that several times that the same cell was changed by different users, the
spreadsheet would save both of the changes, one above the other, while
"inserting" a cell, moving other entries down! If this were to happen to the
live workbook, it would be a catastraphe because the VLOOKUP table would get
skewed, returning the incorrect data.
I did do some further testing, this time putting numbers in column A and
then making the changes in column B and I did not observe this behaviour
again (in the limited testing that I did).
My question is: While Sharing a workbook, can it be gaurenteed that cells
will NOT be "inserted", moving other cells down, or should I simply explore
the other option of an inactivity timer function?
The worksheet here has an index column in column A (numeric data in text
format) and the information to lookup to the left, ideally suited to the
VLOOKUP function.
Steve Welch
(e-mail address removed)
to our entire deparment via lookups from other workbooks (VLOOKUP())
I have this workbook password protected so that it won't be opened for write
unless the person is intending to do some updates in it.
We had a case the other day when one of our techs had opened this workbook
for write and then neglected to either save his work or close the workbook
at the end of the day - he simply locked his laptop and left it docked in
his cube.
The night shift had to go into the workbook and make a correction in it,
but couldn't because it was opened for write by this other individual;
Their solution (since the computer was locked) was to undock the laptop and
shut it down, freeing up the workbook.
Needless to say, he was a bit put out that he had apparently lost all of his
changes! (Hopefully he realizes whose fault this was!)
I am trying to find a solution to occurrances such as this, first I thought
of adding some VBA code that would simply timeout if no activity was
detected and then save and close the workbook - this is still an option.
The other option was to "Share" the workbook so that more than one person
would have write access to it at the same time - this would have prevented
the above incident.
The problem is that when I started testing with a clean workbook, it appears
that several times that the same cell was changed by different users, the
spreadsheet would save both of the changes, one above the other, while
"inserting" a cell, moving other entries down! If this were to happen to the
live workbook, it would be a catastraphe because the VLOOKUP table would get
skewed, returning the incorrect data.
I did do some further testing, this time putting numbers in column A and
then making the changes in column B and I did not observe this behaviour
again (in the limited testing that I did).
My question is: While Sharing a workbook, can it be gaurenteed that cells
will NOT be "inserted", moving other cells down, or should I simply explore
the other option of an inactivity timer function?
The worksheet here has an index column in column A (numeric data in text
format) and the information to lookup to the left, ideally suited to the
VLOOKUP function.
Steve Welch
(e-mail address removed)