Order summary

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!

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!

Bump - Am i being to vague?

thanks!

last try...

no help?

OK..here we go!!

I've created a marco for you.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

At the end of all 8.

Addtionally you may want to increase/decrease the capture range

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

Please Login or Register  to view this content.

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

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.

I would argue "always" is perhaps a tad defeatist

I'd call myself a realist :P

Originally Posted by DonkeyOte

Please Login or Register  to view this content.

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.

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.