Pivot Table search by one term in a cell with multiple terms.

Hello there, 

I am using a Lenovo P1 and working in Office 365 Apps for Enterprise Excel version 2110 (Build 14527.20276), I'm building a workbook to track Bill of Materials for industrial automation roll implementations. We have a parts list with about 500 individual parts. I have a column for "Location". Each cell in this column can have between 1 and 31 location values. I need to make a printable sheet that will give all the parts and quantity of those parts for a given location. Note that a specific part could be found in multiple locations. I was given constraints to only have one line item (row) for each item and to keep my columns as close to one screen width as possible. So now I am on to Pivot tables to create the location item sheet. But I have hit a road block, in that I have been unable to get the table to look for individual terms in the cell and not all the terms in the cell. Also, the reference data is shared by multiple sheets in the workbook. 

How do I get the pivot table to only look at one term in the cell that matches the corresponding item and feeds in the quantity for that item. 

This is the main reference sheet. We can call it "Sheet 1", Note there is a quantity column four columns to the right of the shows cells. I was trying to keep it concise.  

This is my pivot Table found on "Sheet 4"

Row LabelsSum of Qty80/20340-40403Hoffman4AU4024SFF4Grand Total7

I'm going for something like this:  

Choose the area from the drop down at the top. Example "Main"

then every part that has the word "Main" in the location column would show up in the list. 

Thank you for any help you could provide.