Excel 2007 : How to : Auto change the cell and graph format

[amitmodi_mrt]

Hi,

I am working on my team data, I have different sheets, in each sheet i have different month wise data, in few sheet it is in percentage and in few sheets it is in number format. In one sheet i have created a consolidated report and i put a vlookup formula in such a way that what ever report i select from the drop down, it will show me data for that report and will create a graph also. However the problem is when select name of the report that is in number format, it shows me the correct report and graph, but when I select any report which in in percentage format, it convert it into number format.

Is there a way to fix it. I am using Excel 2007

Thanks.
Amit Modi.

[MarvinP]

Check to see if there are non-number values in your "normal" graph.

A sample file would allow us to understand this problem better and give you an answer, better than the above suggestion.

[amitmodi_mrt]

I attached the sheet. In this sheet, (PBA Charts), in the drop down list, first three metrics are in numbers, and lsat two metrics are in percentage.

If you select any one from the last two metrics, Graph is not showing the values in percentage.

[MarvinP]

Can you simply multiply your percent values by 100 to get the chart you want?

Do you expect the chart to say percent somewhere?

[amitmodi_mrt]

Yes, on the axis it should show the percentage.

[MarvinP]

Clicking on the y axis I can get it to display percent but this changes the top 3 charts to percent also. I bet you don't want that. I don't see a way to have this single chart do both number and percent for this chart. Is it possible to build a second chart where the y axis is percent and display it over the top of the existing one?

Sorry for not having a better answer. Perhaps one of the charting gurus will help.