Self populating worksheet.

[Squirbie]

I have initial data in Worksheet 1. Each row has a specified status (Submitted or Outstanding).
I'd like Worksheet 2 to self populate with certain data from only the rows that are marked as Submitted.
I'd like Worksheet 3 to self populate with certain data from only the rows that are marked as Outstanding.

My VLOOKUP formula gives me the data I need but will report results line by line...reporting the "Outstanding" data as "" in the "Submitted" Report.
And reports "Submitted" data as "" in the "Outstanding" Report.

=IF(SDI!$N19=SDI!$N$15,VLOOKUP(SDI!$D19,SDI!$D19:$N$318,1,FALSE),"")

How do I get it to only search for the status I'm specifying?

Can I use VLOOKUP in conjunction with some kind of Data Validation?

 

[mumps]

If you are willing to try a macro instead of a formula, please let me know. I would need to know what column the status (Submitted or Outstanding) is in.

 

[Pup Denab]

Hi Squible
A macro would be a good way to go, however you could just use a pivot table, then filter the table Submitted or Outstanding, it depends on how much data you have, I have a worksheet with 10 thousand rows, and that works great in a pivot table, & is easily updated,
Regards
Pup

 

[Squirbie]

Submitting/Outstanding column is N.
LookUp value is in column D.

 

[Squirbie]

I can't use a pivot table because I'm creating this form to eventually be used by many employees... and they won't know how to run pivot tables. I have to keep it as simple and user freindly as possible.

 

[mumps]

Try this macro. It assumes that your destination sheets are named "Submitted" and Outstanding". If your actual sheet names are different, change the code to suit your needs.

Sub CopyRows()
    Dim bottomN As Integer
    bottomN = Range("N" & Rows.Count).End(xlUp).Row
    Dim rng As Range
    For Each rng In Range("N2:N" & bottomN)
        If rng = "Submitted" Then
            rng.EntireRow.Copy Sheets("Submitted").Range(Rows.Count, "A").End(xlUp).Offset(1, 0)
        ElseIf rng = "Outstanding" Then
            rng.EntireRow.Copy Sheets("Outstanding").Range(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next rng
End Sub

 

[Smitty]

Welcome to the Board!

I can't use a pivot table because I'm creating this form to eventually be used by many employees... and they won't know how to run pivot tables. I have to keep it as simple and user freindly as possible.

Why not? If you mean that they won't know how to refresh the PT's that's easily managed in the Sheet_Activate event.