Chart Using Dynamic Named Range Not Displaying As I Want It

  • I'm attempting to generate a chart by using dynamic named ranges. The column of data that makes up the named range has cells displaying values and then below those are cells that do not yet display values but do contain an underlying formula. The problem I am facing is that the lower cells, the ones that contain a formula but do not yet display a value, are displaying on the chart as 0 values.
    How do I overcome this, so that the chart only displays the preceding, non-zero values?

    In case of need, the dynamic named range Refers to: =OFFSET('D_RT'!$M$61,0,0,COUNTA('D_RT'!$M$61:$M$200),1). Currently the range M61:M83 contains displayed values and the range M84:M200 has a formula in every cell but there is no displayed value.

  • Re: Chart Using Dynamic Named Range Not Displaying As I Want It

    Thanks arthurbr but unfortunately I couldn't find anything on the site to help me. None of the examples in any of the links duplicated my problem. Unfortunately I'm not simply dealing with empty cells that will have data inserted at some later point, which will then dynamically expand the chart. My cells contain an underlying formula and even though the cells appear blank, because the data that will cause the formula to then display a value hasn't yet been entered, the underlying formula is being interpreted as a zero and the chart displays a long line of zero values.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!