Dropdown list to link specific data from other sheets

Since I notice you want your codes parsed as well I used a bit of a different approach. The file is attached.

In B5

Formula:

=INDEX(INDIRECT("'"&$A$1&"'"&"!$A$2"),0)

In C5 and filled across

Formula:

=MID(INDEX(INDIRECT("'"&$A$1&"'"&"!$b$2"),0),COLUMNS($A:A),1)

In A10 and filled across

Formula:

=INDEX(INDIRECT("'"&$A$1&"'"&"!$c$5:$d$7"),COLUMNS($A:A),2)