Consolidate data from 1 worksheet to a summary page

    I am trying to copy the information selected in the "Order form" and consolidate it into the "Order Summary" sheet.


    If someone could help me with a function/formula that searches the "Order Form" and filters all the items that have had a number added in the "Qty" Column and place them and their correlating txt chosen from drop down box into the "Order Summary" sheet it would be greatly appreciated. (Please excuse the messy sheet but had to remove macro's to reduce file size.)


    Thanks in advance


    Brad

    Re: Consolidate data from 1 worksheet to a summary page


    Hi ShosMeister,


    Thanks for the reply and I hope this explains a little better at what I am trying to achieve.


    If you can imagine the order form with a lot more parts, and what happens is that I go through and choose the components relevant to the project i am quoting. What I want the "summary sheet" to do is dynamically recognise those lines that i am selecting in the "order form" and copy that data automatically into the summary field as I go, providing me with a neat summary of only the items i have chosen.


    As requested I have added the data to the summary sheet as it should appear from the items selected in the order form, whilst skipping the unselected parts.


    Hope that makes a bit more sense and truly hope you have a solution for me.


    Thanks heaps


    Brad

    Re: Consolidate data from 1 worksheet to a summary page


    Okay, the issue you are having is trying to get the value out of your control, right? Instead of placing controls on the sheet, why now just use the cell pull-down feature? You already have the lists defined so that's a relatively easy change. Then, your summary just pulls the data from the cells rather than trying to find the value out of the control. Make sense or am I missing something?

    Re: Consolidate data from 1 worksheet to a summary page


    Hi ShosMeister,


    Mmmm, I think i might be missing something. Can you explain to me what you mean by controls? I am a relative novice when it comes to excel so if you have the patience you may to explain it to me step by step or add the example to the xls and re post it if you don't mind.


    Again thanks


    Brad

    Re: Consolidate data from 1 worksheet to a summary page


    Ok I should have done this prior to posting but I have since Youtubed controls and understand what you mean by controls and yes 1 of the problems is extracting the data from the control and the other is for the Summary Sheet to only add the items I select. I ultimately would like to leave the Order Form unchanged if possible.

    Re: Consolidate data from 1 worksheet to a summary page


    So I take it you didn't design the original worksheet then? It may not be able to be done with just cell formulas given your requirement of only displaying the relevant data so it may require VBA code. Not sure if you'd want to go that route just yet given your newness to Excel.


    Also, your example, while okay, has a lot of errors in the worksheet. There are #REF errors in a lot of the pull-downs so it would be best to have a better working copy.

    Re: Consolidate data from 1 worksheet to a summary page


    Yes you right I didn't design the Order Form however I do understand it and could replicate if need be. I realise there is errors in the example I posted but that was due to having to cut out a lot of data. I could email you the complete file if that would be ok. My email is beradster@gmail.com if you want to drop me a quick email and that way I can send the proper file, minus the errors.


    As for VBA I have dabbled a little bit with it but not enough to write a code to solve this problem by myself. Your help would be much appreciated!


    Thanks


    Brad

    Re: Consolidate data from 1 worksheet to a summary page


    E-mailing the file would be fine but remember the rules state that you keep the discussion on the site and don't PM/e-mail individuals for help directly.

    Re: Consolidate data from 1 worksheet to a summary page


    Took a quick look. One caution would be that your "Order" may have a lot more than you have lines for in the Summary. Not really a simple way to handle that, but .....


    Also, can't think of any way to do this without VBA. I'll try to get some work done on it in the next day or so.

    Re: Consolidate data from 1 worksheet to a summary page


    Ur an absolute superstar sir. Thank you very much!


    It's rare that more items are selected then there are lines in the summary sheet so that shouldn't be too big a concern. Is there a reason why you want to avoid using VBA?

    Re: Consolidate data from 1 worksheet to a summary page


    Would just make it more difficult for you to maintain/adjust given your inexperience with VBA.


    Here's the basic process that I'm thinking:
    Since your pull-downs all link to column C, step through Cx to find cells >1. That indicates something was selected there.
    Copy those values from the table using the value in Cx as the index for the text.
    Copy the cost values from the order form.


    Decision: Use a button for manual launch of the macro or make it automatically run somehow based on changes on the invoice sheet.

    Re: Consolidate data from 1 worksheet to a summary page


    The other issue is the extensive number of "lists" that are being used by the pull-downs and none of them are name referenced. That's going to make it impossible to maintain.

    Re: Consolidate data from 1 worksheet to a summary page


    Who created the worksheet? You may need to have them adjust the lists to have defined names which would make things "slightly" easier. Otherwise, it can be done but it will not be very flexible to changes in the lists.

    Re: Consolidate data from 1 worksheet to a summary page


    Hi, sorry for the delayed reply. I am in Australia so by the time you get online it's 12 at night here :P


    The spreadsheet was done by the manufacturer of these products and given to us as a means of quoting and order their materials. I will do my best to redesign the sheet in order to make it more user friendly as you advised and hopefully then we can achieve a better result. Sincerely thank you for trying to help and providing such brilliant communication and advice. A+ ShosMeister

    Re: Consolidate data from 1 worksheet to a summary page


    Here's the issues:


    You have pull-downs that are using a look-up list on another sheet to populate the choices. No big deal as that's the best way to do it. Issue is it's hard coded to an absolute range for each of the pull-downs.


    It is then linked to another cell (the one directly under it) that puts the index, 1 to x, in that cell. That value is then used in another lookup to find the cost associated with that item. Again, not a big problem as that's the normal/best way to do it. Issue is again that it's all hard coded for each cell to the references.


    Now, I have to try to figure out what pull-down has been changed, fairly easy as the index will be >1, and then try to figure out what the value is in that pull-down. That's the difficult part. Since the index is a number from 1 to x, I could then use that number to find the value in the list. The issue is there is no way to identify the list without completely hard coding the lookup. This will result in everything breaking if anything in the table of lookups is changed.


    What I've managed to figure out is that when the workbook is opened, I step through every shape on the "Order Form" worksheet. If it's a pull-down, I then capture the linked cell and reference and store those values in an array. Then, when I find a value that has changed on the Order Form (index >1), I look up in the array the linked cell equal to the cell I'm currently looking at. Then I can get the reference and use the index to get the actual text value.


    I've e-mailed you back the workbook as it's a bit large to be attached here and didn't want to submit it publicly with possible proprietary values. Let me know if it is functioning properly. The macro can be run from the list but could have a button put on the worksheet or even setup to run automatically every time one of the cell values changes on the Order Form.


    The lesson here is that a lot of what you may or may not be able to do with a workbook depends on design so you kind-of have to plan ahead. Keep this in mind when you design other workbooks or try to re-design this one.

Participate now!

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

Register Yourself Login