Dynamic Chart based on filter selection

[cceber]

I have a spreadsheet which contains 1972 rows of data. The spreadsheet contains filters so a user can select to see their specific data (ie, geographic sales region).

The problem is that creating a bar chart of 1972 rows makes it unreadable. So I decided to chart only 30 rows of data. However, now when a user selects their region, it only shows the static 30 rows...meaning it cuts off all data that is in rows 31 through 1972. I'd like the chart to show a dynamic range based on the filter up to a maximum of 30 rows.

I've been reading up on named ranges using offset...but I'm new to this and don't fully understand how to adjust that formula to do what I want. That only seems relevant if I'll be adding more data to my sheet?

Thanks for any help or suggestions!

 

[sijpie]

I have created a large spreadsheet doing exactly this.

The way I have accomplished it is to 'copy values' the filtered sheet to another sheet (normally hidden from view). The copy value copies only the selected rows, none of the others.
Now you have your graphs refer to first thirty rows of the the range on the hidden sheet. (I include all the rows, so I need to first establish the row count, and then set the graphs to that count, I have an 'Update' button that the user presses after he has made his filtered selection in order to set all the graphs correctly)

 

[cceber]

That's a good idea, but I was hoping there would be a way to do this with a dynamic formula or script.

 

[sijpie]

I have not found any way to get a graph out of a filtered range that doesn't inlcude the hidden rows, so that was why a set up the script to do what I describe above. It runs very neatly and quickly.