Cascading dropdown list with dynamic ranges

I have 6 named ranges.

"Sector" Job sector category of each individual, which is a normal named range featuring 5 different sectors:

And the five sectors:
"Administrator", "Manager", "Waitress", "Kitchen" & "Bar"

These are dynamic named ranges which I calculate using =OFFSET(Sheet1!$A$1,0,0,MATCH("*",Sheet1!$A:$A,-1),1)

I am trying to create a cascading dropdown list whereas the 2nd list available is dependent on the sector chosen in the 1st dropdown list in E3.

I would usually use =Indirect(E3) but as the five sectors are dynamic this does not work.

Is there a solution to this problem?

Thanks in advance

Perhaps something like the attached will point you in the right direction

Regards

dav

Hi Dav,

Thanks for the file, have looked at it but unfortunately the town list range does not expand as you add places.

I tried changing the named ranges to =OFFSET(2!$d$4,0,0,MATCH("*",2!$d:$d,-1),1)

but then the dropdown list froze.

any ideas?

Hi,
If you define a name using EVALUATE it will work with dynamic ranges. Choose Insert, Name, Define from the menu, enter List2 (or whatever name you care to choose) and in the refers to box enter this:
=EVALUATE(Sheet1!$E$3)
adjusting the sheet name if required. Then use =List2 as the list source for the second data validation.