Help creating dynamic drop down list

Try

=OFFSET(Dropdown!$A$13,1,MATCH($B$2,Dropdown!$13:$13,0)-1,COUNTA(OFFSET(Dropdown!$A$13,1,MATCH($B$2,Dropdown!$13:$13,0)-1,1000000,1)))

Much easier if named ranges had been used.

You need to reference the Dropdown Sheet in the formula.

How then can I link the drop down matrix with get pivot function?

My interpretation:

Created named range DBLA based on E2 in Dashboard

in "Get Pivot Data"

in B2

=DBLA

in B5

=GETPIVOTDATA("Sum of Smoking Prevalence in adults (18+)%",'Pivot table'!$A$3,"AreaName",$B$2)

Repeat for other