Moving data between columns

[CW]

Good morning,

I wonder if somebody can help me? I am working on a large Excel spread sheet
containing c.20000 rows of data across 28 columns. The cells contain prices
that have been entered in the currency format. The spreadsheet is in regular
use and is often being edited, however it appears that some of the pricing
information is moving between the columns. For example a chunk of data that
was originally entered into column x seems to move into column y or z. This
appears to happen at random and the moving of data is not being performed
intentionally, (I am pretty certain of this as the people responsible for
editing the spreadsheet are also responsible for performing the amendments!).
The spreadsheet is sorted by column headings and we are using the 2000
version.

Any thoughts, comments, or suggestions would be really appreciated.

Many thanks

 

[Pete_UK]

Perhaps someone Inserted some cells, not realising that this action
would move other cells over into another column.

Pete

 

[CW]

Hi Pete,

Thanks for your suggestion. We have had that happen before so we try to
avoid inserting new cells. If we enter new data we tend to add it to the
bottom of the spread sheet and then 'sort' the data. Obviously I can't rule
your theory out entirely, but I wonder if there is another explanation?

 

[M Kan]

If your headers aren't frozen, people can sometimes get confused, e.g.,
column B is showing to the far left and they think it's column A. You could
also try using some data validation in your pricing column to force people to
enter numerical values, which would provide a checkpoint to make sure data is
being entered in the right spots.

 

[CW]

Hello,

Thanks for your suggestion. We have frozen our headers, so that is not an
issue. When new data is input it is entered by one person, saved, and then
checked by another, so we know the data is in the right column when it is
entered. Obviously the occasional mistake may slip through, but not to the
extent that we have encountered. The 'moving data' problem doesn't seem to
arise until some weeks, even months after the data has been entered. Also the
spread sheet is being used by around 10-12 people everyday (as a read only
document) so genuine inputting errors are normally picked up quite quickly.

The spread is sorted regularly, could this cause a problem?

 

[Gord Dibben]

Sorting can cause data to move up or down within a column but won't move it
from one column to another unless you are sorting left to right.

If the workbook is "read only" how can users input data and save the
workbook?


Gord Dibben MS Excel MVP

 

[CW]

Sorry I wasn't very clear, we have a 'live' version of the document, which is
used for editing, and only a couple of people have access to it as it is
password protected. We also create a copy of the 'live' version, which is
read only, that the rest of the department use for reference.

 

[Gord Dibben]

Clears the "read only" part up but I still can't visualize data "moving"
from one column to another without some user intervention.

If data in column X moves to column Y I would suggest someone inserted a new
column left of X


Gord