Graphing based on a Changing Array

Copper Contributor

I have an excel spreadsheet with two columns, 50 rows each. I would like to graph these columns, one on the x-axis (Quantity) and one on the y-axis (Unit Price). The data in the two columns, changes based on a user input box where an item code is specified.

 

For example, item code 2 may only have data for rows 1-12, whereas item code 4 may have data for all 40 rows. How would you recommend I plot this on a chart so that the chart changes as the user specifies the item code they would like to view on the chart?

 

Right now, even with #N/A in the empty rows, the graph still wants to plot everything. I only want it to plot the rows with data. 

 

doncameron_0-1656427126269.png

doncameron_1-1656427191617.png

 

 

1 Reply

@doncameron 

You can define dynamic named ranges for the x-values and y-values.

See the attached sample workbook.

To see the definition of the ranges, click Formulas > Name Manager.