Need Formula

[Guest]

I want to be able to have a row of data automatically imported to another
sheet within the workbook based on the criteria of one cell.

Example:
I have a complete project list and each project has a status I want a copy
of the rows data to be input into another sheet based on the “Status†column.
Such as estimating or in progress. Is this possible?? I don’t want to have
to manually retype a new sheet based on the status of the project.

 

[ilia]

You can filter (Data->Filter->AutoFilter), click the dropdown next to
Status, and select the status you want. Then, copy the rows to a new
worksheet.

This is not quite "automatic". You can use some code to do this
quickly. Assuming the following:
* You have your data on Sheet1, in a range A1:K687
* You have four possible status codes: "Completed", "In Progress",
"Estimating", "Not Started"
* You have four additional worksheets, named by status codes
"Completed", "In Progress", "Estimating", "Not Started"
* You have the status code on your data sheet contained in column 3
* There are no blanks in your data that would cause Excel to not
recognize it as a "region"


Use this code in a standard module of your workbook.

Public Sub StatusReport()
Dim StatusOptions As Variant
Dim rngData As Excel.Range
Dim i As Long

Const intStatusColumn as Long = 3
Const strDataRange As String = "$A$1:$K$687"

Set rngData = Sheet1.Range(strDataRange)

StatusOptions = Array("Completed", "In Progress", "Estimating",
"Not Started")

For i = LBound(StatusOptions) To UBound(StatusOptions)
rngData.AutoFilter intStatusColumn, StatusOptions(i)
rngData.CurrentRegion.Copy _
ThisWorkbook.Worksheets(StatusOptions(i)).Range("A1")
Next i
' clear the filter
rngData.AutoFilter intStatusColumn
End Sub