Dynamically update label axis *format* without VBA? (03 and 07)

[ker_01]

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]

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_DATA,1)

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

Cheers
Andy

 

[ker_01]

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$33>1,(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

 

[ker_01]

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$33>1,OFFSET(A2010BG,1,0),(OFFSET(A2010BG,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...

 

[Andy Pope]

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

 

[ker_01]

And it would appear that even if there is one data series linked to cells
with the correct number formatting, having an additional series that is not
directly linked to cells (e.g., a named range all divided by one) will
interfere with the graph link to the correct formating from the other data
series.

Best,
Keith

 

[ker_01]

I'm not sure if this should be a new thread, but since it is all related I'll
just add it here;

I took out my hidden series and am back to just my regular line. I'm trying
to simplify my data so I can just use the linked format.

I'm trying to get one of my percentage fields working right now. Since my
raw data sheet has formulas in it, Excel was graphing the div/0 values as
zero, so I changed my formula to and if statement;
If(iserror(mycondition),na(),mycondition)
So now the empty cells show N/A and the graph doesn't plot them. The first 2
values are still a percentage, and the whole second series (12 points) are
percentages.

The graph doesn't update the Y-axis to show percentages, it is still showing
just integer numbers based on the last non-percentage metric I viewed.

Does the presence of a N/A value "break" the link to the source range
format, and if so, is there an alternative value I can return from the
formula that won't be graphed and won't break the format link?

Thank you!
Keith