3 Level Dependent Data Validation

Hi All,

This is my first post on this forum.

I have tried to find some examples of threads relevant, but have been unsuccessful in finding exactly what i need.

I have a workbook with three sheets.

Sheet 1 is a large data array (approx 8 columns x max 400 rows), than contains the inventory and cost information of a list of products in a running cafe.
Sheet 2 is is a recipe analysis tool that I want to be able to use data validations to cost out recipes, drawing information from sheet 1 in three dependent drop down boxes 1- category, 2- supplier, 3- item name, which then returns that items cost per standard of measure.
Sheet 3 is where I have started to try to generate a unique list for category and supplier to assist sheet 2 in referencing from sheet 1.

There is data overlap across the inventory items, with many items having the same category and or supplier.

Examples that I have found online use the index and match function, in conjunction with naming to return the array required for the drop down, however most of these examples I have found contain simple unique lists.

I cant seem to nut dynamic naming, which is integral as the list is expanding and contracting all the time.

So far I have been successful by manually naming a range (not a satisfactory solution as per above) and then using the indirect function to reference that named array from sheet 1.

I can send the file if anyone would like to see it!

I am hoping that someone might be able to assist me on this? Any help appreciated!

Thanks

Christian

Thanks for the response. Here is the file.