Deleting from one cell in a row when adding to another cell

[Anomalie]

Office 2000

I update a Work In Progress report at work. I have very littl
experience with Excel so I'm not sure if what I need is formula,
format or a macro.

I use a worksheet that keeps track of where our Customer Returned Good
are in process. Columns P - AL specify a particular area (i.e
Receiving, Test 1, Rework etc.). Each row is a particular product
each one having it's own row. The number of rows change according t
how many CRG's we currently have in house.

Right now, after going around to find all the product, I update th
worksheet by deleting a 1 from one column in the row and adding it t
another column. What I would like to be able to do is add the 1 an
have the old 1 automatically deleted. I.e. a product has moved fro
Receiving to test 1, I put a 1 in the test 1 column and the 1 in th
receiving column is deleted.

First I would like to know if this is possible. And then how do I g
about setting it up for this to happen? Is it possible to only hav
this work when I need to update but not in general so someone couldn'
accidently use it otherwise?

Any input on this would be greatly appreciated as it would lessen th
time it takes to update a WIP of 200+

Joa

 

[Charlie]

If the number 1 is entered in cell A1 for the received
item and cell C1 is the cell for entering the 1 for
testing items then enter in cell A1:
=if(C1=1,"",1). Copy down as needed.

Charlie O'Neill

 

[Trevor]

Joan,

May I suggest an alternative? Instead of columns P - AL, I suggest you use
just 1 column instead, and use a drop-down list to select which area your
CRG is in. Drop Down Lists are good when you want to make only one
selection at a time, and you know your complete list of options. You
enablet he use of Drop Down Lists by using something Excel calls
"Validation".

Try this:
1) Insert a column to the left of column P. Title the new Column P "Area".
2) Now, your areas are listed in columns Q through AM. I'll assume your
titles for these columns are in row 1.
3) Highlight all of column P by clicking on the letter "P" up there. Click
on the "Data" menu and choose "Validation...". For "Allow" choose "List".
In the "Source" type =$Q$1:$AM$1.

You're all set. Now, if you select a cell in column P, you'll notice that a
down arrow appears next tot he cell. You can click on this down arrow and
choose one of your areas from the list. Or you can type one in, but Excel
will only allow you to type an area that is already on the list.

Now, I'm assuming you were using hte number 1 in yuor old method because you
were keeping a total count of the number of CRGs in each area. Instead of
using a SUM function, adding up all the 1's, you will now use a COUNTIF
function.

To accopmlish this, the best thing to do is copy your list of areas
someplace, then put a bunch of COUNTIF functions in the adjacent cells.

For example, try this:
1) Insert a new row 2, just below your titles in row 1.
2) In cell Q2, enter the following formula: =COUNTIF($P:$P,Q1)
3) copy cell Q2 and paste it to all cells R2 through AM2

You'll notice that row 2 now shows a count of the number of CRG in each
area.

Once you undestand how all the formulas work, feel free to move your list of
areas around, and the counts that go along with it. Be careful not to put
anything in a place where it might get deleted as you remove rows of CRGs
once you're no longer tracking them.

Trevor