Linking a Chart to a Spill Range

[rkaczano]

I am trying to graph column of data contained within a spill range. The spill range is based on a SORT() function. Assume the SORT() is creating a spill range from A2:C20.

I cannot seem to XY graph the data in B2:C20. I was assuming that I should try to name the ranges with the data (i.e. create two Named Range for B2:B20 and C2:C20 and then in the chart data dialogue box simply create a reference to “Sheet1!NamedRangeXData” and “Sheet1!NamedRangeYData”). But that did not work either. It will not allow me to make the edit and forces me out of the dialogue box.

I know that saintly accessing a column within a spill range (from another cell within the sheet) usually require something like “Index(SpillRange,,col#)” but I am not sure whether that matters for graphing purposes.

Any ideas on how to link that one ranges in the graph data dialogue box?

Thanks

 

[awoohaw]

can you post the mini workbook using the xl2bb add in? (link below)

 

[awoohaw]

are you sorting by column A? Is Column A your data series labels?

 

[awoohaw]

I'm not sure i unerstand your question. In the following you can see that Cell B3 has been grayed out so the SORT Formula in cell A2 is spilling 3x19. A2:C20
And the chart is based on that.

 

[rkaczano]

Sorry I left out 1 key point. I am trying to make the chart dynamic so that it will update based on the updated spill range. So yes I can link it to the range using a fixed reference, but to make it dynamic I usually would have to name the two ranges to make the series and chart dynamic. That narrative was not in my post and that created some obvious confusion - apologies again. I was having problems creating the two named ranges for the X and Y data in the spill range. I was not sure why this was happening. I need to look at this again today. Thank-you for the quick response.

 

[awoohaw]

Is the size of the range going to change? If it does not just update the formula for the spill range to give the same number of records.
How do you select your records to get into the spilled range?

 

[rkaczano]

Yes the size of the range actually changes. It changes based on an API request to an external website and the # of rows will change each time it runs.

What I was trying to do is create 2 named ranges within the spill range (A2:C20):
NameX ='Sheet1'!$B$2#
NameY ='Sheet'!$C$2#

Then I would go into the chart data and add a series as follows:

Series Name = Nothing
Series X Values = Click on B2 and get reference to 'Sheet1'B2 - delete the "B2" and edit as follows: 'Sheet1'!NameX
Series Y Values = Click on C2 and get reference to 'Sheet1'C2 - delete the "C2" and edit as follows: 'Sheet1'!NameY

When I hit OK what normally should happen is that those references update in the chart data Edit Series dialogue box and look as follows:

='Book1.xlsm'!NameX
='Book1.xls'!NameY

When you click on the X and Y values reference in the Series X Value box you would expect to see B2# and C2# highlight as the should be dynamic ranges. And I should be able to test this elsewhere in the spreadsheet because if I enter = NameX or = NameY the results would spill to whatever cell I am in.

But none of this works and I am guessing that you cannot reference a particular column of a multi-column SORT() spill range by saying "B2#" And this must be why the chart tool cannot pick-up this range.

So I am wondering if I simply make the Named ranges dynamic by using OFFSET() formulas in the Name Manager to get around the limitations in using the "#" operator to reference a column within a spill range? Or if the Offset() function also causes issues on a spill range.

Thanks and sorry for the long post.

 

[awoohaw]

okay, i think you can only do it with vba, then. I am not a vba expert, i'm sorry.

 

[rkaczano]

I did not expect that I would need VBA to do this. Does anyone have any ideas on how to this. Again, I am trying to create a dynamic XY graph that updates based on a spill range that changes in size (row wise) which has the SORT () function in it,

Thanks

 

[rkaczano]

I actually got this to work by naming the individual columns in the spill range as follows:

NameX =Index('Sheet1!A2#,,2,3)
NameY =Index('Sheet1!A2#,,3,3)

Then in the series dialogue box in the chart menu
Series X Values = Click on B2 and get reference to 'Sheet1'B2 - delete the "B2" and edit as follows: 'Sheet1'!NameX
Series Y Values = Click on C2 and get reference to 'Sheet1'C2 - delete the "C2" and edit as follows: 'Sheet1'!NameY

This issue here is that I was trying to name the individual columns in the spill range as:

NameX ='Sheet1'!$B$2#
NameY ='Sheet'!$C$2#

But you can only reference those columns within the spill using an Index.

The chart series now updates when the # of rows increase in the spill range as the named range uses the index() function to properly access the column data.