Generating a data summary sheet based on multiple criteria

Dear experts,

Please refer to the attached data sheet.

These are some data available in a garment manufacturing unit.

In "Data Entry" sheet I have to enter values(Input, LIne out, QC out and Packing in) as shown by some example values. Two major criteria upon which the data filtering/sorting should be done are
" PO(Purchase Order)" and "Colour".

A PO can have more than one colour. And, the quantities are entered in to the "Data Entry" sheet PO wise, colour wise and size wise. These data can be "input", "Line Out", "QC Out" or "Packing in".

My requirement is to display these values in a sheet as " Summary". This summary should be generated automatically based on the values entered in to the data entry sheet. Detail labels such as "Input", Line Out", QC out" and "Packing in" should be shown if the size wise data are available or not(if values are not available, "0" value should be shown)

I would suggest normalizing your data and inserting a pivot table. Read this for starters

I am not an expert in excel, I don't know how to do this with a pivot table...

Thanks

Pls let me know how to refresh the Pivot table?

Hi,

Also, I need to have something like a drop down list to be appear in columns C and D (in the data entry sheet)so that the the details " colour" and "PO" can be selected. PO and Colour information can be pre-entered into rows and columns in the data entry sheet before the data entry begins.

Hi,

Find the updated sheet with helper Column .

Punnam

Thanks a lot. However, I need these "Colour" and "PO" selection labels to appear in the "Data Entry Sheet" tab, not in the summary sheet. Pls re do and send the work sheet to me along with the "Pivot Table" sheet tab(the same document you sent to me first)..and data entry tab sheet.

hi,

have checked my post 8 ?

Punnam

Yes, but you have removed the Pivot table tab that was added by Boopathiraha. I will attached the same here, and pls do this "selection thing" in that and send me back pls.

thanks in advance.

hi,

So u required only pivot table ?
I am not good in pivot tables.

Punnam

Hi Boopathi,

Thanks a lot for your solution. However, there is a blank row in the pivot table, and don't know how to remove it. Pls explain me how to do it.

Also, can I have "Sub Totals" for each PO?


The file is also attached herewith.

Best regards.

Hi Boopathi,

Thanks a lot for your solution. However, there is a blank row in the pivot table, and don't know how to remove it. Pls explain me how to do it.

Also, can I have "Sub Totals" for each PO?


The file is also attached herewith. Pls help, this is very important in my job..

Best regards.

Hi Boopathi,

Thanks a lot for your solution. However, there is a blank row in the pivot table, and don't know how to remove it. Pls explain me how to do it.

Also, can I have "Sub Totals" for each PO?


The file is also attached herewith. Pls help, this is very important in my job..

Best regards.

Hi Punnam,

Can you please create a "drop down" list for "PO" and "Colour" columns so that I can select the PO numbers and Colour details pre entered in the column "U". The drop down list doesn't need to do any calculations, it should help only for "selecting" the particular PO or colours easily(reduce typing) when the data is entered in the "data entry sheet"

Thanks in advance.

is this what have u asked for ?

Punnam