Dynamic chart with variable rows in data

[mark hansen]

I struggled with this for a week and looked on multiple sites for help. I need to create a chart that has multiple straight lines, each representing a person's beginning and ending score.

I created a grid that lists the people in each group (groups are selectable) and I pull the data from a data set for each person in the group. The people and data are pulled using a array formula using INDEX and MATCH. I have dynamic ranges set up for the people column and each column in the data.

When I create the chart I have the people listed in the X axis and on the chart is two lines with one line on the beginning score and one on the ending score. So far So Good. Switching groups works as expected --- more or fewer people represented on the chart.

When I switch row and column I get what I want --- at first. One straight line for each person going from their beginning score to their ending score.

The problems start when I switch groups. The grid with the data is working fine, but if the new group (selected in the drop down) has more people, some get cut out of the chart. If the new group select in the drop down has less people, I have lines in the legend without a name and all the lines without a person just draw on the zero line. Its like the dynamic ranges switch to a fixed range.

Anyone have at ideas?
Mark Hansen