Display data label: Number vs. %

[Aland2929]

I want to display either a % or a number based on the selection of an option button. My problem is with switching between the number format and % format. I have attached a test file.
Thanks in advance.

[gjcase]

The problem is that B18:B24 is formatted as Percent. Thus, even when you select PBIT, it is presented as a percentage. Suggest you format as a number, and multiply your percents by 100 to have them display properly.

If you want to have the data displayed as percentages, then you probably need a macro to change the formatting of that range. You can easily record a macro to do the formatting, and then assign it to your option button. You'll need another for the other option as well.

[Aland2929]

Thanks for your reply. I have written the macros and attached them to the option buttons & it works perfectly.

[Andy Pope]

You don't need code you can use named ranges to chart the selected values.

[Aland2929]

Thanks Andy. Could you provide an example of that based on my test file?

[Aland2929]

I have attached my sample file with the macros.

[Andy Pope]

I alter the labelling of the options buttons so the store value makes the Offset named range work easier.

[Aland2929]

That is so much simpler. Much appreciated.

[gjcase]

Hey, Andy, that's a neat trick. Thanks for the tip.

[Aland2929]

I understand the offset function. Can you explain the range for the chart? If I select the chart, I can't see the data range for the chart?

[Andy Pope]

Use the Select Data dialog to see the named range used.

[Aland2929]

It says the data range is too complicated to be displayed?

[Andy Pope]

I don't get that message anywhere.

Use Formulas > Name Manager, to see the named range formula.
If you select the columns the range used has a box around it.
If you use Select Data you see the formula used to reference the named range

[Aland2929]

OK, I finally figured it out. Thanks for all your help.