Consolidating data from multiple worksheets

Hi,

I have a workbook that has several different worksheets. Each worksheet is a region and has same headlines. In the sample file attached, the production # field is unique number. I would like to have a summary table that would allow to user to find a production code for each item by filtering region, section, type and endorsement number. I was thinking of using pivot table consolidating ranges but it didn't give me the right result. Could anyone have any idea how to do it?

Many thanks in advance!
Ann