Generating Pivot Table from multiple worksheet

[jackysoh]

Hi there,
i have 3 work sheets with these data fields: RO, ROC, CO
RO is customers order where the new orders for the model will be placed.
the data field is "model","po number", "ord qty", "del date"

ROC is when customer request to make changes to the RO they already made
the data field is "model","po number", "change qty", "change date"

CO is issued when the customer finally confirms their order
the data field is "model","po number", "del qty", "del date"

As you all may seen, the model name and po number must matched up in these 3
fields.
I want to use pivot table function to make a table that sort according to
model name, po number and shows the RO qty, del date, then shows ROC qty and
dat, and the CO qty and date.
but for the pivot table to work, i have to join the info's of the RO, ROC,
CO work sheet together according to the model name and po number. Although
using vlookup function to match and then copy the qty and date info is
relatively straight forward,

IS THERE ANYWAY I CAN create multiple pivot table field lists (eg. RO, ROC,
CO field list) and throw the info into a single pivot table, so that Excel
will automatically group according to model name and po number for me?

i hope i post my question clear and understandable. really appreciate the
feedback from you all.
thank you all very much.

 

[krcowen]

You can make a pivot table from multiple consolidation ranges (the
third option donw on the first pivot table wizard form). What you can
do with pivot tables made from multiple consolidation ranges is pretty
limited though. If you simply want to count the various quantities
but model number it might work for you, but, most likely you will need
to consolidate your data then create the pivot table from the
consolidated data to get much value from it.

Good luck.

Ken
Norfolk, Va