Can you not put formulas into chart data ranges in Calc? How else can you control a chart's range and x-axis label range with one cell, without having to manually go in to the chart for every change? Clarification in details

Libreoffice Calc v. English
Linux Mint 16 English

I am making a histogram, and I’d like to be able to control the number of bins it has. I have everything working; a list of data for the counts of each bin, and another list with the text of the ranges of each bin. The chart will react correctly to changes in the cells. But I want to be able to change the number of bins, and have the chart update automatically. What I tried to do was something like this:

CHART/Data Ranges/Data Series Tab/Range for Y-Values: A1:INDEX(A1:A10, F1)

Where F1 has the number of bins I want, and A1:A10 has the counts of the various bins. Other logic determines what is actually in the cells, depending on how many bins there are, and is working perfectly. But when I try putting the above formula in the above spot, it turns red and won’t let me click ok. This leads me to believe I’m doing something wrong, but I don’t know what. Is this not allowed, and if so, how else should I do it? I googled already, and found some things that almost seemed to do what I want, but I didn’t know how to use them. One thing I found wanted me to define an object with Ctrl+F3, but that seems more involved than what I want.

Any ideas?

I think no formulas to define chart ranges, even not named ranges.

Maybe filtering data can help, see attached sample.
Advanced filter can do it from a value in a cell.


There are a few histogram questions in this forum. Enter “Histogram” in the field “search or ask your question” and check out the answers there. They might have found a solution or workaround.