# 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

Have you considered just putting an interpolated number there?

Bruce :cool:

• 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

## Participate now!

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