Without VBA code the only way to change the axis number format is if it
is linked to cells that have the correct number formatting.

Cheers
Andy

On 16/03/2010 23:21, ker_01 wrote:
I added a new named range (and added it to the graph as a data series); I set
it so that the range would always evaluate to "1"s so that my percentage
charts would max at 100% and it would have no impact on my integer charts:
=IF(Sheet2!$D$331,OFFSET(A2010BG,1,0),(OFFSET(A20 10BG,1,0))/(OFFSET(A2010BG,1,0)))

Where OFFSET(A2010BG,1,0) just contains 1 in each cell.

So now on each graph I have (if I did it properly) one data series that is
divided (and therefore should not affect the axis format) and one data series
that isn't divided, and should affect the axis format.

However, upon testing, I see my new series (and it always =1) but I'm
getting no impact on the Y axis format- it doesn't change when I change my
data source from a percentage to numeric metric...



"ker_01" wrote:

Andy-

Thank you for your reply. I had checked the "linked to source" box in the
axis formatting, without result- now I know why. I'm still kinda stuck on
potential solutions.

All of my raw data is on one spreadsheet, so I set up an indirect range
reference by setting one range (2009 data) to O1:Z1 and another (2010 data)
to (AA1:AL1). I then have two additional pieces of data in my lookup table;
how many rows to offset (to get down to the row of data associated with this
metric) and a divisor; some of the numbers (like financials) are expressed in
thousands, so I divide by 1000. Others, like my percents, don't need to be
changed at all, so I divide by 1.

Dividing in my named range formula resulted in Excel losing the source
format, and because my graph started with a number format, it was obvious
when looking at percentage metrics because they were expressed as decimals.

This named range:
=(OFFSET(A2010BG,Sheet2!$D$32,0))/Sheet2!$D$33
would not bring over the source percentage formatting; but this updated
formula does:
=IF(Sheet2!$D$331,(OFFSET(A2010BG,Sheet2!$D$32,0) )/Sheet2!$D$33,OFFSET(A2010BG,Sheet2!$D$32,0))

The problem is that some of my values do still have a divisor, and they
therefore still lose the link to the source formatting. When selected after
another format (for example, I select percentage then revenue) the previous
formatting is retained by the graph, even though it is not the desired format.

So now, my graph (with a numeric Y-Axis) looks fine until I select a %
metric; the Y axis updates accordingly to show percents, but then when I
select a non-percentage (financial) percentage metric again, the Y-Axis stays
as a percentage.

Since I'm losing the linked formatting, is there any other way to
dynamically force the axis format without VBA? I'm thinking there may be
some obscure solution that involves a hidden data series that would
conditionally calculate to a numeric range (and therefore force the linked
formatting) but would not evaluate when the source value was a percentage.
I'll have to play around to see if I can figure out a way to make that work.

If there is a straightforward way to do this, I'd still welcome any guidance!

Thank you,
Keith



"Andy Pope" wrote:

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
D25 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
.


--

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