Problem: Chart dynamically updates source data depending on drop list selection.

Solution:

B4 is a drop list:

1. Click tab "Data"
2. Click "Data validation"
3. Click "Settings" tab
4. Click "List"
5. Click "Source" button to select source area

   

6. Click OK!

B5 to B7 get values dynamically, depending on drop list selection (B4):

B5: =HLOOKUP($B$4,$C$4:$F$7,2,FALSE)
B6: =HLOOKUP($B$4,$C$4:$F$7,3,FALSE)
B7: =HLOOKUP($B$4,$C$4:$F$7,4,FALSE)

The chart gets source data from range: A5:A7 and B5:B7

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Looks for a value in the top row of a table or array of values and returns the value in the same column from a row you specifiy