get zeros in data table of chart

[Mifty]

Hi,

I have a master sheet which returns data based on an index/match formula. When the school number is changed in cell AQ1 all the data ranges for the charts in the sheet are recalculated to reflect the specific data for that school. The aim is to have a generic report which pulls data automatically in to the master sheet report. I've got some code which will change the school number automatically in AQ1 based on a list and makes a value only copy of the sheet for each school. Some schools do subjects that other schools don't but so that we have a generic report, charts with data tables are displayed for all subjects.

The index/match formulas returns 0 if the range is blank but if I change the range to text with ' then the index match returns a blank, the chart based on the range is then blank but the data table dislays zeros.

Is there anything I can do so that values are automatically dispayed when they are returned but that blanks are not displayed as zeros on the data table of the chart?
Many thanks
Mifty

 

[Steve=True]

Hi Mifty,

Welcome to the formums.

Generally, if i don't want a data point to show, i have the formula return:

NA() instead of 2 quotes (blank)

Most Excel Charts don't plot #NA

example: =if(a1=0,NA(),a1)

Hope this helps

Steve=True
www.exceldashboardtemplates.com