Chart Conditional Updating

**nhojflies** · 04-28-2009, 09:48 AM

I have a standard table with dates running down the y and categories along the x. I've set up a line graph using Jon Peltier's system of naming ranges and using OFFSET. The chart is great: as I add data - the chart expands to represent this.

However I really only want the chart to expand when there is some data in both axis

Currently when I use fill to add a big batch of dates (the idea being that a new date doesn't have to be added to the table every week), the result is that the graph expands. But I don't want the chart to expand as there is no data to plot against the dates.

Is there a way to do this? Or a workaround which would limit the amount of manual updating to the sheet?

Thanks

**sweep** · 04-28-2009, 10:11 AM

Hi,

Yes, you can name the range of data, and then use that named range in the offset function to name the dates. That way, the dates range will only be as long as the data range. Eg

If you have dates in Column A and Data in Column B, use the following named ranges.

DataPoints = OFFSET(B1,0,0,count(B:B),1)
Dates = OFFSET(DataPoints,0,-1)

**nhojflies** · 04-28-2009, 10:52 AM

Thanks for your reply

I don't think this quite works because the data comes to the table in the following way:

A macro code prints out the names of all the worksheets in the workbook in the first column.

The data in all the columns of the table apart from the first one (which is the date column) is created by looking at the relevant spreadsheet and cell no. for each cell.

I don't want my users to have to fill down the formula each week OR create new worksheets.

Ideally there are at least 10 empty spreadsheets (each representing 1 week) with the appropriate naming in the workbook, in my "date" column there's the 10 dates representing Week Commencing dates and the formulas for all the cells relevant to those dates are present in the columns to the right - just waiting for the data to be entered in the appropriate spreadsheets.

The other problem is that the presence of those formulas having been entered in anticipation means that "0"s are thrown up instead of the cells remaining blank - this in turn expands the graph which I don't want - and in another table which refers this one, throws up DIV/0 errors.

Sorry that this is such a long reply but it is ( at least to me ) quite a complicated problem.

Thanks

**sweep** · 04-28-2009, 10:57 AM

Can you post an example of the format of your workbook?

**nhojflies** · 04-28-2009, 11:20 AM

Here is a pared down version

Thanks

**sweep** · 04-28-2009, 11:30 AM

...and you're using controls to view a specific region of chart. What you're after can be achieved, but it'll take a little code. Will take a look at it this evening for you.

**nhojflies** · 04-28-2009, 11:38 AM

very much appreciate it

The controls are pretty much an optional extra I haven't properly sorted them yet. - I'd like to use them if possible but they are not vital

Thanks again

**nhojflies** · 04-29-2009, 11:38 AM

Hi,

I've got round the issue of "0"s appearing by making "NA()" appear in blank cell situations

Stats sheet

=IF(ISBLANK(INDIRECT(A1&"J2")), "NA()",INDIRECT(A1&"J2"))

Percentages Sheet

=IF(ISBLANK(Statistics!D4),"NA()",IF(Statistics!D4="NA()","NA()",IF(Statistics!D6=0,"0",(SUM(Statistics!D4)/Statistics!C4)*100)))

The NA won't be plotted in the line graphs where the 0s were when I filled all the cells with the formula in anticipation. (I'm going to make the font colour white or something when the result is "NA()" to make it look better.

I can use a similar method to make the dates "appear" and disappear so that they only "appear" when there is something in a particular cell in that date's worksheet" eg the first record - so the date would appear only when the sheet starts being used

Assuming that works I think I've pretty much cracked it - thought I'd let you know in case you were going to have another look at it - I'll let you know how I get on

thanks again for all your help