Dynamically Changing Charts from % to Numbers

[xcave]

Hi,

I would like to create a dashboard in which users can choose to view metrics in either percentage or raw volume format. I have created a drop down menu in cell E11 that allows users to select either "percentage" or "volume" from a list. Then I entered the following formula:


B2=IF($E$11="percent",0.5,TEXT(400,"##,#"))


The default format of cell B2 is to display values in percentage terms. My problem is that when I create a column chart that displays the value in B2 along the y-axis, the chart is unable to dynamically change with the drop-down menu from percentage to volume. Instead, when I select "volume" from the list the chart just goes blank. Why is this the case, and what can I do to resolve the issue?

Thank you

[martindwilson]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Steve=True]

Hi Xcave,

Welcome to the forums.

Here is my sample file: How-to Make a Chart Display Volume or Percentage with a Picklist.xls

Also, this post should help with this as well:
How-to Create Dynamic Excel Dashboards Using Picklists

Let me know if it helps.

Steve=True