Dynamic Ranges With Offset Function

You can make very professional looking interactive charts by using dynamic ranges. In this post, I’ll show you how to set up dynamic ranges with offset function. I have previously posted a small tutorial about Offset Function. This post will help you better understand the concept.

Dymanic ranges means data ranges that changes dynamically with changing data. When you use this ranges as your charts’ or calculations source data, you will have dynamic sheets. Here is how to set up dynamic ranges with offset function:

We have a data table like the one below for 12 months in “data” sheet:

Now insert a data validation list into a cell and set Nr column of your data table as source data. This will be our changing data.

Now open Name Manager in Formulas ribbon and define a new name like shown below.

This formula means range between “C3 in data sheet” and cell that is as downwards as selected month.

Now this range is tied to our month selection. As selected month changes, MC range will become shorter or longer.

Insert a sparkline into interface sheet (sheet1) and set it up like shown below.

You can also add a sum of complaints in any cell you choose. Formula will be “=SUM(MC)” that’s all. Now you have a sparklines chart that dynamically changes with month selection. You can also use this method in regular charts. We will also come to that 🙂

Here is the finished product for your inspection and practice: Dynamic Ranges With Offset Function Example