Summarize Customer Order Data

  • Problem
    I have created a simple order form to take customer orders . I would like to summarize the orders on a summary page .(The order Form will be very long so i don't want to print the entire document every time .
    I have started to create a summary page using information from a similar post on this forum. I am having trouble creating a summary when the products on my order form have multiple sizes rather than just one


    I am new to using excel .

  • Re: Customer Order Summary


    Hi Dan


    I understand you are new to excel, Dave has a lot of resources on this site which will help you get started. I recommend reading the "Excel Best Practices" article found here:


    http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm


    then reconsider the design of your order form. There is also "sticky" articles which will always be at the top of this forum, which offers great advice.


    Cheers
    Jamie

  • Re: Customer Order Summary


    Dan, as suggested by Jamie. You should lay out your raw data in Excel as Excel expects. I.e. a classic table (database) format, then you can present & summarize data anyway you wish. PivotTable are ideal for most situations.

  • Re: Summarize Customer Order Data


    Just to clarify Dave's comment about "classic table format".


    This means:

    • Having a single row at the top of the table as the header row.
    • Each cell in the header row contains text (usually) that indicates the type of data to be entered below.
    • All of the rows below the header are "records" - each row being a single record and each cell in the record row is a "field" of data.
    • The table should not have any blank rows or columns. Blank cells in a record are OK.


    One suggestion after looking at your file:
    Create a "Product Table" on a separate sheet. This table should contain all relevant data - item description, codes, sizes, etc. Use this as a look table, Data Validation Lists, etc.


    Take a little time to review the information on Dynamic Named Ranges and Data Validation.


    With a properly structured data table, you can summarize, extract, etc. data very easily and quickly.

  • Re: Summarize Customer Order Data


    Thanks for the feedback . After reading the link on layout I realize now that my layout is not really correct for this to work properly . The summary table was an afterthought when i finished the order form .


    Im going to start fresh when i make the new order form later in the year .Hopefully a bit more excel competent .


    Does someone have an example spreadsheet that uses a data table in this way

  • Re: Summarize Customer Order Data


    Quote

    Does someone have an example spreadsheet that uses a data table in this way


    The attached is a very basic example, just to illustrate the concepts.
    It contains the following:

    • Product Data Table
    • Order Entry Sheet
    • Order Records
    • Customer Data Table
    • Pivot Table Summary


    Dynamic Named Ranges and Data Validation (using list option) is used and the Pivot Table is just a simple summary - nothing complex


    Simple VLOOKUP formula are used to populate cells in the Order Entry based selection from a drop down list.


    Again, this is not a fully developed solution, just a sample to get you started and guide you along.

  • Re: Summarize Customer Order Data


    I have started again from a blank sheet . This time the products and separated into data tables and the order summary on a separate tab .


    I can get the order summary table to pull data from one data table perfectly . And it doing exactly what i want it to do .


    I now want it to pull data into the order summary from 2 or more data tables . How would i get it to perform this action and am i heading in the right direction with this new order form (attached)


    Thanks

  • Re: Summarize Customer Order Data


    Dan,


    I think you're headed in the right direction.
    Because the structure of "Item Data 1" and "Item Data 2" are identical, albeit with different data, you could use just a single table for all similar product data.


    As for the Item Data sheets, it seems to me you are combining a product data table and an order entry (quantity) method.
    Ultimately, you have to develop a system that serves your specific needs but, IMO, the Item Data sheet should be just that - a table of product data for reference, nothing else.


    Another sheet should serve as the Order Entry which pulls information (using look up formulas) from the Item Data sheet and allows entry of quantities, etc. Then develop your approach to summarize the order entry - for example, the Pivot Table in the example file.


    Your "look up" method on the Order Summary sheet is something I've never seen before and it is dependent on hard-coded values in other cells to provide inputs to offset formulas.
    It is more efficient and flexible to use a "standard" look up formula like I showed in my example workbook. There are many look up methods, such as Index/Match formulas that you may want to investigate.


    Before you attempt to develop the workbook, give more thought to structure and design and do a bit of reading on using dynamic named ranges, data validation, dynamic look ups. There is lot of information on this forum on those topics. Visit the download page for working examples.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!