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.
|