Dynamic Chart problem

**jtd84** · 10-04-2010, 07:04 PM

Hi,

I'm trying to set up a dynamic chart but am having a little difficulty as I've never done it before. I've found some articles that are good but they're all vertical tables whereas I need to do it with a horizontal table. I've attached what I have so far so you can see what I'm doing. At the moment, the date range goes to 28 Feb. I've put a formula into the Count row so that if the date=0 (i.e. anything after Feb 28) then show nothing, otherwise pull the data from sheet1. In sheet1 there are values after Feb 28 because the original file had rolled up 13 week values. This is why i've used a formula to make the count=0 if the date=0. The data will change each time a new user uses the file so that dates will also change. I've purposely placed the graph in the 'Graphs' tab as i want all the workings on hidden sheets and the graphs on a front page. I've defined the names for the dynamic chart (which aren't working) below:

PurchaseValues =OFFSET(Sheet2!$D$3,0,0,1, COUNTIF(Sheet2!$D$3:$W$3, ">0"))
PurchaseDates=OFFSET(PurchaserValues,1,0)

This is probably pretty straight forward (I hope) but i'm really struggling at the moment.

Thanks,
John

**Palmetto** · 10-04-2010, 08:49 PM

I left your original named range formulas so you could compare the differences with mine:

PurchaserValues2 : =Sheet2!$A$3$D$3,0,MATCH(Sheet2!$D$8,Sheet2!$D$2:$W$2,0),1, COUNTA(Sheet2!$2:$2)-MATCH(Sheet2!$D$8,Sheet2!$D$2:$W$2,0))

PurchaseDates2 : =OFFSET(PurchaserValues2,-1,0)

Explore the chart series.
You should set the chart X-axis to display as Category so that Excel doesn't interpolate and add extra dates on the axis.

**jtd84** · 10-04-2010, 09:11 PM

Hi,

I'm not sure I understand what you've done. It looks like you've excluded all the dates before Dec 13 and then included the non existent dates. What i was hoping was that the graph would pick up the first 'real' date and end on the last. The non existent dates that follow will pull through but i want those excluded. There will always be non existent dates at the end because the raw data works off lookups and the date range always changes. That's why i had a formula in that turns the qtr count to 0 if the date=0. I've attached my workbook again with some changes i made. It looks like it should work because the formulas are picking up the correct ranges but i can't seem to graph it with the formulas i have. My new formulas are:

PurchaseDates=OFFSET(Sheet2!$D$2,0,0,1, COUNTIF(Sheet2!$D$2:$W$2, ">0"))
PurchaseValues=OFFSET(PurchaseDates,1,0)

Could you have a look and let me know what i've done wrong?

Thanks,
John

**Palmetto** · 10-04-2010, 09:55 PM

I'm not sure I understand what you've done. It looks like you've excluded all the dates before Dec 13 and then included the non existent dates. What i was hoping was that the graph would pick up the first 'real' date and end on the last.

Your "Total Qtr Count" formula contains if($D$2=$D$8 . . . . .).
I simply placed a date value into D8 on the premise that you wanted to match an input date and start the dynamic range from that point. Nothing was excluded.

The only way to end the range on a "real date" is to NOT include pseduo-dates. If the raw data works off of look ups then you should return blanks or zero and change the COUNTA formula in the Dynamic Named Range to A Countif(range,">0").

Don't have time just now to look at the attachment.