Scrap vs Ship Pivot Table

[jvrsshurricane]

Hello,
I am trying to somehow combine two separate sheets from the same workbook so that I can make pivot tables and compare what we have scrapped vs what we have shipped in both qty and dollar amounts. I have tried alt+D >P and selected the columns to include from each worksheet but am assuming that it doesn't work because the columns aren't side by side. So I tried copying and pasting with paste link as the ship data sheet is linked to access and the scrap data sheet is the one I tried to paste link as I want the info to update as more data is added. I am unfamiliar with VBA code at this point but trying to learn Access and thinking maybe it would be easier to do in Access as I know I can edit relationships since both sheets have item codes, dates, and pricing. Any help would be greatly appreciated. I have attached a sample file to give you an idea of what I am trying to do. I am using Excel 2010.

[oeldere]

Please show the expeced result in your excel file (and mark the related cells with the same background color).

[jvrsshurricane]

I have updated the sample table. I would like to compare the pivot tables on the Scrap tab with the tables on the Ship tab. Does this help in my explaining my dilemma?

[oeldere]

Maybe like this.

with all data in 1 worksheet.

[jvrsshurricane]

Sweet! Yes, this is what I was after. What steps/functions did you use to create this? I appreciate your quick response and help.

[oeldere]

b9 =Ship!D2
c9 =Ship!A2 etc

and drag down.

In column A you find the sheetname (manualy input).

It will be a lot easier if you add all data in 1 sheet.

[jvrsshurricane]

Ok thank you very much!