Pivot Tables from consolidated data ranges

Dear all,

I wish to make a pivot table which summarises the purchases I need to make, across multiple worksheets.

I wish to make a device which contains a number of circuit boards. Each circuit board has an associated list of components that need to be purchased. At the moment I have the list of purchases required for each circuit board on it's own worksheet within a single workbook. (see attached example)

Note that some components are required for both circuit boards, so appear in both worksheets.

When I make a pivot table from a single worksheet it behaves exactly as I would expect it to. However, as soon as I choose to use a consolidated data source I get some weird results - the main one being the field list doesn't contain all the field options (for some reason it also fills in my pivot table with data without my permission!) Does anyone know why this is the case and how to fix it?