complicated problem...simple solutions?

[julietjuliet]

Hi!
I am trying to create a workbook with some customizable graphs. I have followed this wonderful article (google "Excel Dynamic Named Ranges = Never Manually Updating Your Charts") so far. But my data is a little more complex and I am now stuck. My data is the daily sales of 14 locations. I want to filter it (i.e. choose just one location from the 14) before I graph it. How do i do that?

(In the attached file PK is a primary key, not really useful when it comes to graphing. Location is the locations ID number. ccTotal, q87 and q91 are the sale values I am logging. One of these sales values is sufficient per graph)

My final ambition is to be able to compare the locations sale over time on one graph. Is that possible?

thanks very much!

 

[Bob Phillips]

I cannot see your issue, the chart seems a simple pull from the data, so setting a dynamic range on that data should be trivial.

What is not working for you?

 

[julietjuliet]

Hi, youre right the dynamic range is straightforward. I would use...
OFFSET ( DATA!$A$2,0,0 COUNTA (DATA!$A:$A)-1, COUNTA (DATA!$1:$1))

BUT, when i graph that for the q87 sales values, each day will have 14 sales values (for 14 locations). Now I could just use auto-filter. What I need to do is create some kind of control (a list box or let the end user just enter the location number in a blank cell and refer to that) to select the location number. Then the dynamic range needs to filter itself according to that

thanks for the reply!