Dynamic Named Ranges with Intermittent Blanks

  • So I have a spreadsheet that is used to track the Weight and measurements for myself in regards to my workout regimen, to track my weight loss progress.


    This is a generalization of how I have things set up and what it would look like with the gap


    [TABLE="width: 500"]

    [tr]


    [td]

    Start

    [/td]


    [td]

    1/1/2016

    [/td]


    [td]

    300

    [/td]


    [/tr]


    [tr]


    [td]

    Week 2

    [/td]


    [td]

    1/8/2016

    [/td]


    [td]

    295

    [/td]


    [/tr]


    [tr]


    [td]

    Week 3

    [/td]


    [td]

    1/15/2016

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Week 4

    [/td]


    [td]

    1/22/2016

    [/td]


    [td]

    290

    [/td]


    [/tr]


    [tr]


    [td]

    Week 5

    [/td]


    [td]

    1/29/2016

    [/td]


    [td]

    292

    [/td]


    [/tr]


    [/TABLE]



    I have defined a dynamic range for Weight with the following formula.


    =OFFSET(Measurements!$C$10,0,0,COUNTA(Measurements!$C$9:$C784))


    I typically weigh myself every week, but occasionally I miss a week.


    When I go to chart this data I get a gap in the chart where the blank is, the chart adds the other points but does not continue the line past the blank.


    Any help in creating a dynamic range that can be charted without gaps would be appreciated.


    Thanks

  • Re: Dynamic Named Ranges with Intermittent Blanks


    Try this. It will work.


    =OFFSET(Measurements!$C$10,0,0,LOOKUP(2,1/(Measurements!$C$10:$C$784<>""),ROW(C:C)))

    One idiot throws a stone in the well, and it takes a hundred wise men to get it out.

  • Re: Dynamic Named Ranges with Intermittent Blanks


    You cannot have a dynamic named range with gaps. The data should be entered as zeros if not available. Also, you ought to consider using a Table instead of defining a range