+ Reply to Thread
Results 1 to 15 of 15

Order summary

  1. #1
    Registered User
    Join Date
    06-25-2007
    Location
    USA
    MS-Off Ver
    MS Office 2010
    Posts
    28

    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!

  2. #2
    Registered User
    Join Date
    06-25-2007
    Location
    USA
    MS-Off Ver
    MS Office 2010
    Posts
    28

    Re: Order summary

    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!
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-25-2007
    Location
    USA
    MS-Off Ver
    MS Office 2010
    Posts
    28

    Re: Order summary

    Bump - Am i being to vague?

    thanks!

  4. #4
    Registered User
    Join Date
    06-25-2007
    Location
    USA
    MS-Off Ver
    MS Office 2010
    Posts
    28

    Re: Order summary

    last try...

    no help?

  5. #5
    Valued Forum Contributor jj72uk's Avatar
    Join Date
    03-22-2008
    Location
    Essex, England
    MS-Off Ver
    Work - Office 2000, Home - Office 2007E
    Posts
    360

    Re: Order summary

    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

  6. #6
    Registered User
    Join Date
    06-25-2007
    Location
    USA
    MS-Off Ver
    MS Office 2010
    Posts
    28

    Re: Order summary

    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,
    Last edited by njg2982; 11-01-2009 at 08:09 PM.

  7. #7
    Valued Forum Contributor jj72uk's Avatar
    Join Date
    03-22-2008
    Location
    Essex, England
    MS-Off Ver
    Work - Office 2000, Home - Office 2007E
    Posts
    360

    Re: Order summary

    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

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Order summary

    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

    Please Login or Register  to view this content.
    ... 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.
    Last edited by DonkeyOte; 11-02-2009 at 06:33 AM. Reason: numerous typos!

  9. #9
    Valued Forum Contributor jj72uk's Avatar
    Join Date
    03-22-2008
    Location
    Essex, England
    MS-Off Ver
    Work - Office 2000, Home - Office 2007E
    Posts
    360

    Re: Order summary

    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.

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Order summary

    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!)

  11. #11
    Valued Forum Contributor jj72uk's Avatar
    Join Date
    03-22-2008
    Location
    Essex, England
    MS-Off Ver
    Work - Office 2000, Home - Office 2007E
    Posts
    360

    Re: Order summary

    I would argue "always" is perhaps a tad defeatist
    I'd call myself a realist :P

  12. #12
    Registered User
    Join Date
    06-25-2007
    Location
    USA
    MS-Off Ver
    MS Office 2010
    Posts
    28

    Re: Order summary

    Quote Originally Posted by DonkeyOte View Post
    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.

  13. #13
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Order summary

    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)

  14. #14
    Registered User
    Join Date
    06-25-2007
    Location
    USA
    MS-Off Ver
    MS Office 2010
    Posts
    28

    Re: Order summary

    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.

  15. #15
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Post Re: Order summary

    Apologies for delay - slipped under the radar!

    Please Login or Register  to view this content.
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1