I have two sheets in a workbook. Sheet 1 has six columns of information about
projects. One of the columns contains one or zero indicating status [open or
closed]. Sheet 2 has array formulas that copy only those rows from sheet 1
that have open projects. In addition there is a comment column on sheet 2,
where we can update current activities on the open projects. These comments
are updated frequently while project is open.
Problem: Let's say sheet1 has 140 projects and 50 of them are open. Sheet 2
will copy all the info. for those 50 projects and create a nice compact
report. I will go in and add/update comments for all projects. Next day if
one of the projects on sheet 1 is closed, I'll mark it as such on sheet1.
When sheet 2 is updated that closed project disappears [as it should], but
its comments will remain in place. When other rows are copied, the comments
are now misaligned from their projects. Is there any way to create a link or
connection between the comment when its written first time or subsequently
updated and one of the columns on that row [say, project name], so that next
time around when that project name moves to a different row, the comments
from previous update would follow and be written in corresponding row?