View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
MyVeryOwnSelf[_2_] MyVeryOwnSelf[_2_] is offline
external usenet poster
 
Posts: 143
Default Automatically move data from one worksheet to another

I have an excel file that is tracking projects completion and status.
Within the file I have 2 worksheets ("In Progress" and "Completed").
Within the "In Progress" worksheet, I have columns capturing the
project name, assigned date, assigned by, estimated completion date,
comments, etc. I also have a column that has an IF statements and
adds the letter C when the completed date is entered. I wanted the
whole row to transfer over to the "Completed" worksheet when this C
appeared on the "In Progress" worksheet.


Here's one way using Excel 2003, without macros.

In the example I used, cells A2:E13 of "In Progress" contain the project
data, and Column F is the one with the possible "C" values.

In the "Completed" sheet, put these values.

In A2 put
=IF('In Progress'!F2="C",MAX(A$1:A1)+1,"")

In B2 put
=IF(ROW()MAX($A:$A)+1,"",
OFFSET('In Progress'!$A$1,
MATCH(ROW()-1,$A:$A,0)-1,
COLUMN()-2))

Extend B2 rightward to F2.

Select A2:F2 and extend down to row 13.

Hide column A.

Modify to suit.