Dynamic chart with variable rows in data

mark hansen

Well-known Member
Joined
Mar 6, 2006
Messages
534
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top