Cutting Rows and Pasting Further Down the Same Worksheet

[sspatriots]

My company has several Excel files that are set up for the user to cut a row out of the upper portion of the spreadsheet and move it down in the same worksheet when the status changes. For example, the first 100 rows may be "Active" jobs. Then there are about 10 blank rows, the next row is just a new heading in a single cell that says "Jobs On Hold", beneath that will be all the jobs that were cut from the "Active" area above and pasted beneath this "Jobs On Hold". The same worksheet is set up to move jobs down to a "Cancelled Jobs", "Complete & Not Shipped" and "Complete and Shipped" sections further down the spreadsheet.

I've suggested eliminating the blank rows and adding a narrow column with the following legend at the top of the spreadsheet somewhere:

Job Status Legend:
1 AJW - Active Job, Working
2 CJS - Completed Job, Shipped
3 CJN - Completed Job, Not Shipped
4 HLD - Hold
5 CXL - Cancelled

I know that every time they cut and paste rows down a spreadsheet that the conditional formatting gets all jacked up. I'm hoping someone can read this and give me other pros and cons on the current method that is being used so that I can convince management to let me change the current set up to what I have suggested.

Thoughts please.

 

[mumps]

I would suggest using a separate worksheet for each Job Status.

 

[sspatriots]

That's the way it was prior to the way it is now and there were a lot of issues getting people to move stuff to another worksheet, so they went with this method. One of the problems I see with it now besides what the cut and paste does to conditional formatting, is that every once in a while someone will sort that spreadsheet. There is no numbering system to sort it back to where everything falls back under the 5 sections of status like it was before the sort. No numbering system. Sequential numbers get moved down with the rows every time a user moves a row down. So if they sort it by that column, it will move items of the other 4 status into with the "Active" items and you have no way of knowing where they were moved to when moved down before the sort.

 

[mumps]

Using separate sheets would probably solve that problem. A macro could automate the moving or copying of the row when the status changes. It would not have to be done manually. So when a user enters a status, that row would automatically be copied/moved to the sheet matching that status. If you insert a drop down list in the status column, the action would be performed as soon as the status is selected form the drop down list and this would also save the user from having to type the status in manually. If this would work for you, it would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[sspatriots]

They are fixated on keeping it all on one worksheet. I went ahead and added a status column. They can sort on that now and keep all on the same worksheet.