Data Series Labels and Values from Spill Data

[TASTEIN00]

I have one more question along the same lines as my previous post...

So this is my current series formula: =SERIES(,'Ag Program Scholarship'!$B$4:$B$21,'Ag Program Scholarship'!$C$4:$C$21,1). Column B is my labels and Column C is my values.

So my Series data labels and values are coming spill data that is returning, is it possible to use that in a formula as data labels/values instead of hardcoding the ranges in. Everything that I'm seeing is to use named ranges, but I don't really want to have to create the same named range for 30+ sheets. Especially if new scholarship funds were added, and a new worksheet had to be added, we would have to create a named range for the new sheet.

I've tried using the hash, but it didn't like that either. It would be nice so it would automatically change each month when new data was added. And you can't return a spilled range into a table, so that eliminates the idea of creating a table.

Hopefully this makes sense to you.

 

[Peter Bartholomew]

I think the thing to do is accept the fact that named ranges are the preferred option. If you use names localised to the sheet, then copying the sheet will create the new names automatically. I think that the chart calculation is fairly antiquated and will not accept dynamic ranges unless they are used within a defined name.

I gave up the practice of using direct cell references in 2015, so my challenges are not the same as yours.

Chart series
=SERIES(,Sheet1!label,Sheet1!value,1)

Name Sheet1!label refers to
=Sheet1!$A$4#

Name Sheet1!value refers to
=Sheet1!$B$4#