Dependent Drop Down list based on Pivot

I've been trying to create a couple of drop down lists that are dependent on data in a pivot table. Basically,it related to a raw materials in a drop down list as parent and the child list is a list of all batch numbers relating to that raw material.

I've used this contextures blog as a base and managed to get it work, if I edit the sample sheet and change the pivot data to my data source but if I do it an a new sheet of my own design, it doesn't work. It's something to do with the relative cell reference names. There's a note on the sheet that reads - "Note that the StateNo and StateCityNo range names only work in Sheet1. They use relative references to read the value of the state from the cell to the left of the active cell. If you want to use the data validation drop-downs in a different Sheet, you would need to create a new set of range names specific to that Sheet." I'm not sure what this means or what I need to change in my external sheet. The cell it references in the Name Manager seems to change when I change the drop down options.

[blog.contextures.com/archives/2010/03/31/dependent-data-validation-from-pivot-tables]

Apologies for the vagueness. I'll do my best to answer any questions and try upload my sheet later. I'm using Excel 2013 too.

1. Update your profile about Excel version
2. Attach desensitized example excel workbook with BEFORE and AFTER

To attach an Excel file to your post,

• desensitize data
• remeber that your example should reflect structure and type of data
• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

DataValPivot - is the Contextures sample
Drop Down Test - is my edit.

It works for me in the test file I edited, however if I recreate it from scratch in a new sheet, it doesn't.