Indirect Vlookup List and Dynamic Named Ranges - Is there a workaround

I am creating an ROI tracker, where I need to link via a lookup table to dynamic ranges, dependent on business area. The column where the validated list will be populated is currently C. The formulae to create each dynamic ranges are as follows:

Please Login or Register  to view this content.

Each dynamic range has a list of advisers, that can change on a weekly basis. I have used non-dynamic ranges extended beyond the current last populated row, but the drop-down list then starts at the first empty cell, rather than at the top, which makes it rather useless. I cannot enter a blank at the top of the range, as each range is a pivot table, where the report filter is selecting the business unit applicable to the list.

I can reference the dynamic lists directly, but the following formula, which I would usually use to find the list, does not work when a dynamic range is used.

Please Login or Register  to view this content.

The AreaLookup table is as follows:

Please Login or Register  to view this content.

Is there a way that I can produce the same results, with dynamic lists of adviser names, using perhaps VBA? I did see one suggestion here, but was not sure how to apply it to my project: URL Removed as requested

Hi
external links being potentially unsafe or access denied by member's admins, it's best to post the file on the forum