Axes formatting XY graph

**nomeames** · 08-07-2009, 09:31 AM

Hi,

I am trying to create a dynamic XY graph once with dates in X axis and once with "No. of weeks". See the attached example sheet; Is it possible...

1. to set the formatting of X axis to change with the selection. (i.e to date format when choosing date and number format when choosing no. of weeks).

2. In case of city D and E, choose only the cells with data and ignore the blank cells?

Thanks in advance.

**masteff** · 08-12-2009, 12:30 AM

I can only think to do it w/ macros attached to the drop downs:

1) in your graph go into "format axis" and on "number" tab, check "linked to source". Then add a macro like this to your date/week drop down:

Please Login or Register  to view this content.

2) Add a macro like this to your city drop down:

Please Login or Register  to view this content.

Note that you could make the ranges dynamic but that's a bit more complicated. You can probably search on methods for setting dynamic ranges in macros if you want to go that route.

Also note that certain elements will change if you implement this in another spreadsheet (ie, the chart won't always be "Chart 3", etc).

(colors added for emphasis only)

**Andy Pope** · 08-12-2009, 04:57 AM

No need for code.

Set the custom number format for the xaxis to
[>30000]dd/mm/yyyy;0

In order to ignore the missing data you need to add a couple of named ranges,

XDATA: =OFFSET(Sheet1!$B$36,0,0,Sheet1!$F$30,1)
YDATA: =OFFSET(XDATA,0,1)

You will need to add a helper row to your table which reports number of data points in each column. This information is picked up by formula in F30.

=HLOOKUP(VALUE(CONCATENATE($B$30,$D$30)),$B$3:$L$29,27,FALSE)