Order summary

[njg2982]

I have 8 Sheets in my worksheet with orders from my customers. Column C is their PO# and Column M is the Status of the order. What im looking to do is set up a summary sheet with the List of 8 customers that tells me what PO#'s are in a particular status.
I need a formula to list all PO's that are in "Fabricating" or "Completed" Statuses.

I have gotten this far:
=if(Sheet1!M25="Fabricating",Sheet1!C25,if(Sheet1!M25="Completed",Sheet1!C25,"None"
which works perfectly for the individual rows, but i need it to do it for all rows (1-2500). If i do Sheet1!M2:M2500, i loose it.

Please help!

Thanks!

[njg2982]

OK, So...

Upon further trial and error i have found that:
=IF(Store1!$M$2:$M$2491="Fabricating",Store1!$C$2:$C$2491,IF(Store1!$M$2:$M$2491="Completed",Store1!$C$2:$C$2491,"None"))

Does work, only it pulls the PO# directly from the exact location and does not make it the next in the list.

I have attached what i have so far.

Please help.

thanks!

[njg2982]

Bump - Am i being to vague?

thanks!

[njg2982]

last try...

no help?

[jj72uk]

OK..here we go!!

I've created a marco for you.

Sub datagrab()
    Sheets("Store1").Select
    Cells.Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=13, Criteria1:="=Completed", Operator:=xlOr, _
        Criteria2:="=Fabricating"
    Range("C3:C10000").Select
    Selection.Copy
    Sheets("Summary").Select
    Range("B5").Select
    ActiveSheet.Paste
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
End Sub

Add it to a module and attach it to a button and run it.

To explain:

- It goes into your store 1 sheet
- Applies an auto filter
- Filters the status column to only show completed or fabricating
- Copies from the PO column ( C3:C10000)
- Returns to your summary sheet and pastes them into B5 downwards
- Highlights the B column and removes any empty cells so there are no gaps.

I added that last point in as you said you had 8 sheets, so there would be gaps using my above method.
But the last point removes this problem.

Just make sure you have

Selection.SpecialCells(xlCellTypeBlanks).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp

At the end of all 8.

Addtionally you may want to increase/decrease the capture range

[njg2982]

Thanks for responding!

This is very close to what I'm looking for!! My only question i have at this point for this solution is if i can get rid of the auto-filter on "Sheet1" after using the macro, as i still need to review all data on this sheet.

An additional inquiry is can i do multiple cells that correlate to the PO "copied" i.e. If i Need to have PO, Salesperson, and Product rather than just the PO cell?

alternatively are there any solutions that do not require macros?

Thank for your help,

[jj72uk]

To get rid of the auto filter just use this amended code:

Sub Macro2()
    Sheets("Store1").Select
    Cells.Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=13, Criteria1:="=Completed", Operator:=xlOr, _
        Criteria2:="=Fabricating"
    Range("C3:C10000").Select
    Selection.Copy
    Sheets("Summary").Select
    Range("B5").Select
    ActiveSheet.Paste
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Sheets("Store1").Select
    Cells.Select
    Selection.AutoFilter
    Range("A2").Select
    Sheets("Summary").Select
End Sub

An additional inquiry is can i do multiple cells that correlate to the PO "copied" i.e. If i Need to have PO, Salesperson, and Product rather than just the PO cell

This can be included in the above macro if needed. You'd just need to define what other ranges to copy and paste

alternatively are there any solutions that do not require macros

Not that I am aware of. If you have ~2500 rows of data you want to look at, then in your summary sheet you'd need ~2500 cells to look at each indervidual row to see whether or not there is a desired result, and then you'd have lots of gaps EG you may have data in C3:C10 and then a load of N/A's (or blanks if you use ISNA) until more data.

Unless someone else knows another way...a macro is the way to go

[DonkeyOte]

IMO (FWIW) you should look to aggregate the store sheets into one central repository sheet (via Code if you wish to keep store sheets separate) with one additional column to flag store.

You can then in turn use this central sheet as the source for a Pivot Table which will provide you with immense reporting power & flexibility for little or no effort on your part.

The code to aggregate the sheets would be very straightforward.

jj72uk - wherever possible avoid .Select etc - this will only slow your code.

Re: Macro2 - this could be re-written .Select less and condensed as follows

Sub Macro2()
With Sheets("Store1")
    With .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp).Offset(, 13))
        .AutoFilter Field:=13, Criteria1:="Completed", Operator:=xlOr, Criteria2:="Fabricating"
        Intersect(.Offset(1).Resize(.Rows.Count-1).SpecialCells(xlCellTypeVisible),.Columns(3)).Copy Sheets("Summary").Range("B5")
        .AutoFilter
    End With
End With
End Sub

... it's also worth noting that altering row visibility is a Volatile action so it's generally a good idea to manage Application level settings like Calculation (in addition to screenupdate etc...) when applying Filters etc... to try and keep performance as keen as possible.

[njg2982]

Sub Macro2()
With Sheets("Store1")
    With .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp).Offset(, 13))
        .AutoFilter Field:=13, Criteria1:="Completed", Operator:=xlOr, Criteria2:="Fabricating"
        Intersect(.Offset(1).Resize(.Rows.Count-1).SpecialCells(xlCellTypeVisible),.Columns(3)).Copy Sheets("Summary").Range("B5")
        .AutoFilter
    End With
End With
End Sub

Don - This is much more efficient and will work perfectly. My only additional request is how can i modify this code to not only include the PO but Material and Salesperson?

Im not really familiar with coding yet. so any help is appreciated.

Thanks to both of you for replying.

[DonkeyOte]

I can't see a Material column in your sample file (I can see Salesperson)

I am guessing each sheet will be a different column on Summary tab ?
(ie Store 2 will be in C, Store 3 in D and so on and so forth)

[jj72uk]

Thanks Don,

I knew my solution wouldn't be the best as my coding is still on a basic level.

The OP seemed desperate so I thought I'd try to help.

Your expertise will always surpass mine.

[DonkeyOte]

Your expertise will always surpass mine.

everything is relative ... I would argue "always" is perhaps a tad defeatist

Incidentally, please don't take offence, the post was only meant as advice ...
You would be amazed at how much faster code will run if you can avoid selecting objects, for other little nuggets of "best practice" see:

http://blogs.msdn.com/excel/archive/...practices.aspx

I certainly don't adhere to them all in my code but I try to use as many as I can - nobody's perfect
(well perhaps only a handful!)

[jj72uk]

I would argue "always" is perhaps a tad defeatist

I'd call myself a realist :P

[njg2982]

I apologize - Material = Product.

I'm still trying to determine how i will arrange them on the summary sheet. if i do columns i would need 3 columns for each item returned (PO, Salesperson, and Product), which I'm not sure i want... I guess for now ill just focus on the PO only.

Don, your code pulls the data from the store sheets very fast but it does not update them if i change the orders status and run the macro again. it keeps the PO's from the first run. The previous code worked perfectly but it was quite slow.

[DonkeyOte]

Apologies for delay - slipped under the radar!

Sub Macro2()
Dim xlCalc As XlCalculation
On Error GoTo ExitPoint
With Application
    xlCalc = .Calculation
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
    With .Sheets("Summary")
        .Range(.Cells(5, "B"), .Cells(.Rows.Count, "B").End(xlUp).Offset(-(.Cells(5, "B") = ""), 2)).Clear
    End With
    With .Sheets("Store1")
        With .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp).Offset(, 13))
            .AutoFilter Field:=13, Criteria1:="Completed", Operator:=xlOr, Criteria2:="Fabricating"
            Intersect(.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible), .Range("C:C,F:G")).Copy Sheets("Summary").Range("B5")
            .AutoFilter
        End With
    End With
ExitPoint:
    .Calculation = xlCalc
    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub

The above does what you wanted I believe in terms of store1 - you now need to decide where you intend to store the data for each store on the summary tab such that you can alter the above accordingly.

Note: in the above I implemented the earlier points re: App level settings to improve performance.