Dependent Data Validation across workbooks. Source data frequently sorted and manipulated

[highlifesipper]

Image of (2) workbooks:
https://drive.google.com/file/d/0B7BUBKK8yXCUV2c3V2NSMTlNLVE/view?usp=sharing

I want to be able to compile parts data in one workbook. "MASTER PARTS LIST.xlsx"
https://drive.google.com/file/d/0B7BUBKK8yXCUMnFBZDV1alFNQ2c/view?usp=sharing

Then I want to create a list of parts for a particular project. "PARTS NEEDED FOR SMITH PROJECT.xlsx" that pulls data from the "MASTER PARTS LIST.xlsx"
https://drive.google.com/file/d/0B7BUBKK8yXCUOVBqT3F4X2x4V00/view?usp=sharing

Specifically, I need "PART" column within "PARTS NEEDED FOR SMITH PROJECT.xlsx" to reference "PART" column within "MASTER PARTS LIST.xlsx".

Then, I need "VENDOR" column within "PARTS NEEDED FOR SMITH PROJECT.xlsx" to reference "VENDOR1, VENDOR2, VENDOR3, VENDOR4" columns "MASTER PARTS LIST.xlsx" and be dependent on the text in "PART" column.

As you can see the data set in each workbook I have set up as a table.

I also need to be able to frequently add records, sort and filter the data within "MASTER PARTS LIST.xlsx."

The text within the "PART" column of "MASTER PARTS LIST.xlsx" will need to contain spaces, dashes, double quotes and single quotes. These symbols are inherent to the parts we use and are common identifiers within the industry.

I almost got this to work using dynamic named ranges, but my named ranges contained underscores "_" that replaced the spaces, dashes, double quotes and single quotes. I'm confused about the substitute function.

Also, my named ranges within "MASTER PARTS LIST.xlsx" went from left to right within a row whereas the text in "PART" was the name of the range for "VENDOR1, VENDOR2, VENDOR3 and VENDOR4". When I sort the rows my named range locations would get all messed up. I tried using INDIRECT within named range refer to parameter and removing the "$" signs within the formula.

I can't get it to work properly.