hi there. you can use 2 methods here. but before you do that, convert Work task A1:C6 to a Table first. just click on A1 and press CTRL + T. that gives you a dynamic range where you don't have to constantly change the range. the first method is via an array formula. in cell A2:
Formula:
=IFERROR(INDEX(Table1[WORK TASK CODE],SMALL(IF(Table1[TASK COMPLETED DATE]<>"",ROW(Table1[WORK TASK CODE])),ROWS(A$2:A2))-ROW('Work task'!$A$2)+1),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
same as the other 2 formulas in B2 and C2:
Formula:
=IFERROR(INDEX(Table1[TASK COMPLETED BY],SMALL(IF(Table1[TASK COMPLETED DATE]<>"",ROW(Table1[WORK TASK CODE])),ROWS(B$2:B2))-ROW('Work task'!$A$2)+1),"")
Formula:
=IFERROR(INDEX(Table1[TASK COMPLETED DATE],SMALL(IF(Table1[TASK COMPLETED DATE]<>"",ROW(Table1[WORK TASK CODE])),ROWS(C$2:C2))-ROW('Work task'!$A$2)+1),"")
the other is through something called worksheet event. it triggers a macro after a particular worksheet is activated.
Private Sub Worksheet_Activate()
With Sheets("Work task")
LR = .Cells(Rows.Count, "A").End(xlUp).Row
For RowX = 2 To LR
If .Cells(RowX, "C") <> vbNullString Then
NR = Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(NR, "A").Resize(1, 3) = .Cells(RowX, "A").Resize(1, 3).Value
End If
Next
End With
MsgBox "Updated!"
End Sub
How to install your new code
- Copy the Excel VBA code
- Select the workbook in which you want to store the Excel VBA code
- Press Alt+F11 to open the Visual Basic Editor
- Press CTRL+R to ensure Project Explorer window is on the left
- Edit > Paste the macro into the right window that appeared
- Close the VBEditor
- Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
To run the Excel VBA code:[*]Select the Work Completed worksheet[/list]
by the way, do mark threads as "Solved" once it answers your question. you can refer to my signature on some guides.
Bookmarks