Hi,

In a very quick test, in both 03 and 07, if the named range was
reference the data cells with appropriate number formatting that was
dynamically adjusted in the chart.

My test data set was in the range A1:E5. Row 1 had series name. Column A
had category labels.
B2:B5 were percentages
C2:C5 were currency
D2:D5 4 decimal place values
E2:E5 General.

In H2 was a data validation list of the series names B1:E1
In I2 a formula, =MATCH(H2,B1:E1,0), which provides the offset for the
dynamic range.

CHT_DATA: =OFFSET(Sheet1!$A$2,0,Sheet1!$I$2,4,1)

The series formula was,
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$5,Book1!CHT_DAT A,1)

Change the selection of H2 caused the Y axis to change number format.

Cheers
Andy

On 16/03/2010 19:43, ker_01 wrote:
Workbook currently being developed in 2003, but will be used in both 2003 and
2007.

I have a chart that has dynamic (named range) sources. Users can pick the
metric of interest (revenue, %attendance, etc) and their location, and the
graph will update with the appropriate data.

The problem is the Y-Axis; I haven't figured out how to force the format to
a particular type (number, dollars, percentage, etc) without using VBA. If I
set the axis as percent and then select a revenue graph, I get super huge
percentages instead of the desired number format (and visa versa).

Is there any way to link the axis format to a cell, range, formula, or
anything else without using VBA? My users would not reliably enable macros,
so VBA isn't an ideal solution.

I welcome your tips, tricks, and recommendations. I googled, but all the
hits were general dynamic charts or other non-applicable results.

Thank you!
Keith

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info