A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old March 16th, 2010, 08:43 PM posted to microsoft.public.excel.charting
ker_01
external usenet poster
 
Posts: 87
Default Dynamically update label axis *format* without VBA? (03 and 07)

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
  #2  
Old March 16th, 2010, 09:41 PM posted to microsoft.public.excel.charting
Andy Pope
external usenet poster
 
Posts: 2,088
Default Dynamically update label axis *format* without VBA? (03 and 07)

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
  #3  
Old March 16th, 2010, 11:41 PM posted to microsoft.public.excel.charting
ker_01
external usenet poster
 
Posts: 87
Default Dynamically update label axis *format* without VBA? (03 and 07

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
.

  #4  
Old March 17th, 2010, 12:21 AM posted to microsoft.public.excel.charting
ker_01
external usenet poster
 
Posts: 87
Default Dynamically update label axis *format* without VBA? (03 and 07

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
.

  #5  
Old March 17th, 2010, 09:38 AM posted to microsoft.public.excel.charting
Andy Pope
external usenet poster
 
Posts: 2,088
Default Dynamically update label axis *format* without VBA? (03 and 07

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

  #6  
Old March 17th, 2010, 03:51 PM posted to microsoft.public.excel.charting
ker_01
external usenet poster
 
Posts: 87
Default Dynamically update label axis *format* without VBA? (03 and 07

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


"Andy Pope" wrote:

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

.

  #7  
Old March 18th, 2010, 12:59 AM posted to microsoft.public.excel.charting
ker_01
external usenet poster
 
Posts: 87
Default Dynamically update label axis *format* without VBA? (03 and 07

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

"ker_01" wrote:

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


"Andy Pope" wrote:

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

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:23 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.