Multiple level data validation across worksheet with common name for final level

[SamHorlick]

Hi everyone, this is my first post. I am trying to set up a 3 level drop down list using data validation with the 'INDIRECT' function. The problem arises because the second level requires data groups with the same name.

I am trying to identify where we have the most problems in a chemical plant. Each level is a more specific location- the final level being the exact equipment.

• Level 1- area
• Level 2- type of equipment
• Level 3- equipment number

All areas are different but also have the same types of equipment though- pumps, valves, etc. When I try to save a new data set as valve, excel will tell me I already used this name for a different area. I have tried naming sets of data to only their respective sheet to solve this problem, but then the main sheet where the actual drop down list is doesn't recognize the names.

For clarity I will just show what I am trying to accomplish. (let me know if this link doesn't work). Notice how level one ends up at the same name for level 2 multiple times. (a-1, b-1)

I know this is tough to explain, so if you have questions, please ask.