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:

=OFFSET('Team List Data'!$N$7,0,0,COUNTA('Team List Data'!$N$7:$N$350),1)
=OFFSET('Team List Data'!$Q$7,0,0,COUNTA('Team List Data'!$Q$7:$Q$350),1)
=OFFSET('Team List Data'!$T$7,0,0,COUNTA('Team List Data'!$T$7:$T$350),1)
=OFFSET('Team List Data'!$W$7,0,0,COUNTA('Team List Data'!$W$7:$W$350),1)
=OFFSET('Team List Data'!$Z$7,0,0,COUNTA('Team List Data'!$Z$7:$Z$350),1)
=OFFSET('Team List Data'!$AC$7,0,0,COUNTA('Team List Data'!$AC$7:$AC$350),1)
=OFFSET('Team List Data'!$AF$7,0,0,COUNTA('Team List Data'!$AF$7:$AF$350),1)
=OFFSET('Team List Data'!$AI$7,0,0,COUNTA('Team List Data'!$AI$7:$AI$350),1)

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.

=INDIRECT(VLOOKUP($K$3,AreaLookup,2,0))

The AreaLookup table is as follows:

CAM                    CAM
COM                    COM
Credit                  Credit
CSR                     CSR
Multi Channel         MCT
Other                   Other
Outbound              Outbound
SST                     SST

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