Named Ranges: Dynamic Range for Chart Data Series

=OFFSET(Calcs!$I$1,1,0,(Counta(Calcs!$I$!:$I$1000)-COUNTIF(Calcs!$I$1:$I$1000,"")))

WHAT IS THIS?!?  Well, let's see...

I use this formula as the value for a named range on a spreadsheet that is nothing but formulas that are pulling data together from a "Raw Data" spreadsheet.

The formula essentially looks within the range I1:I1000 and defines the range that represents all cells with data in them, or are not equal to "" or blank.

Let's break it down by each formula used:

• =Offset(reference, rows, cols, [height], [width]) - Straight from the formula description: "Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference returned can be a single cell or a range of cells." 
• =Counta(value1, value2...) - Used to count the number of cells within a range that contain text/formulas
• =Countif(range, criteria) - a conditional statement that allows you to count the number of cells that meet a single criteria that you've specified.

If we were to read the formula in simplified layman's terms we would say: Starting at cell I1 and staying within the same column, count the number of rows/cells that contain a value that is not blank or "".  Then return the overall range as the final calculated value.

This is why we can use this named range as a data series in a chart.  If the data grows, the formula recognizes that because another cell is no longer blank, and since the chart is using the named range with the formula, the data series reflects that.

One caveat to this formula, if you have a null value in the original data set, the cell value on the spreadsheet with the subset of data cannot = "" or blank.  It will need to read "Null" or "NA" or something of the like - otherwise the above formula stops when it finds the blank and will not reflect well in the chart.

Hope this helps!