Copying a Range of Data Dynamically?

[terrydunne]

I am building a spreadsheet that tracks the status of projects in m
department. Status is determined by a *Status * column on the *Data
worksheet that lists the project as -Complete,- -In progress-, -O
hold,- etc. When a project is complete, we copy the rows containing i
from the *Data * worksheet to the *Completed * worksheet on the sam
spreadsheet. However, the rows containing the completed projects mus
remain on the Data worksheet and therein lies the problem—the tw
worksheets are not dynamic and the same data has to be entered in tw
places. I want to make the two worksheets dynamic.

What I need is a function that will copy dynamically the row
containing the completed projects from the Data worksheet to th
Completed worksheet. The function needs to read the Status column o
the Data worksheet and, if the project is listed as Complete, then i
must copy that row, in this case from columns A–M, to the Complete
worksheet.

I realize this involves an IF function, something lik
=IF(Data!K2:K100,"Complete", copy that row to "Complete!").

Can anyone offer any advice on this?

Thanks,
Terr

 

[Anne Troy]

Hi, Terry. This will require VBA. Formulas don't "copy" for you.
Something like this might give you a start:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=318

*******************
~Anne Troy

www.OfficeArticles.com

 

[terrydunne]

Thanks Anne! It looks complcated but I'll give it a try—Terr

 

[Debra Dalgleish]

If possible, I'd keep all the data on one sheet, then use AutoFilters,
or pivot tables, to summarize and report on the data.

However, you could add a column on the Data sheet, to number the items
that are completed. For example, with Status in column H, enter the
following formula in cell A2, and copy down to the last row of data:

=IF(H2="Complete",MAX($A$1:$A1)+1,"")

On the Completed sheet, use VLookup formulas to return the data for the
numbered items. There's a sample here:

http://www.contextures.com/excelfiles.html

Under Functions, look for 'Dynamic List'

 

[terrydunne]

Thanks Debra. It looks like a good way to go---Terry