Microsoft Excel - Dynamic Excel Charts - Asked By shekhar on 02-May-18 08:12 AM

Hi ,

I have data from Jan through Dec in rows.

Row 1 all months 

Row 2 - 2018 data till say 4 months .....from 5th month i have a formula in place which would automatically pick up                                                                     when i move to next month

Row 3 - 2017 data for all 12 months

Here is the sample data:

Months Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2018 171 179 124 63 - - - - - - - -
2017 92 83 116 99 121 91 181 148 125 114 140 161


I want to plot this data in a line chart with months in X axis.  I want 2018 data to be plotted only till Apr as that is my current month. When i move to May, chart should automatically pick May data.

For 2017 it should plot all the months data. I have a control sheet from where i pick the current month from a list box.

Problem that i face as of now is , for 2018 the line drops down to 0 from May to Dec.
STEPHEN DRULEY replied to shekhar on 09-Sep-18 05:51 AM
Instead of using "-" as a no entry, use NA().