Dynamic Name Range In Excel with Offset Function & Table

Dynamic Name Range In Excel with Offset Function & Table ,Dynamic Name Range,Name Range In Excel,techinfo,support.office.com,excel- name range in excel 2010,using named range in excel,name range in excel 2013,Name range,change name range in excel,Dynamic name range,create name range in excel 2016,name range in excel,name in range in excel,Offset function, easy,ablebits,exceljet,extendoffice,techrepublic,windmill,excelfunctions

In Excel, Dynamic name range using is so easy and time-saving job that is also known as expanding ranges. The dynamic name range automatically expands and contracts to accommodate new or deleted data in a worksheet. Suppose you are making a report for daily, weekly, monthly, or any period from your input data with select your desired range repeatedly. But creating a dynamic name range only one time helps to automatically any report.  Here I show how to create a dynamic name range by Offset function and by the table and how to get benefits from this range.

(1) OFFSET formula with a defined name.

One way to create a dynamic named range with a formula is to use the OFFSET function together with the COUNTA function.

OFFSET function to generate a range that expands and contracts by adjusting height and width based on a count of non-empty cells.

Example:

If you want to create a dynamic named range with an offset function that helps to expand your desired data automatically you can do it easily & when new items are added, the range will automatically expand.

Dynamic named ranges create in the Name Box dropdown list. However, you can type the names in the Name Box, to select that range on the worksheet.

By following  image:

1.  The first select range where you want to create a range

2.  On the Ribbon, click the Formulas tab then define the name
3.   Type a name for the range,

In the Refers To box, enter the Offset formula that will create a dynamic named range of  size, based on the number of items in the column,

Offset function (Syntax)- have five argument -(reference, rows, cols, [height], [width])

Reference -  The reference from which you want to base the offset. Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.
Rows  - The number of rows, up or down, that you want the upper-left cell to refer to. Rows can be positive (which means the starting reference) or negative (which means the starting reference).

Columns -The number of columns, to the left or right, that you want the upper-left cell of the result to refer to. Cols can be positive (which means to the right of the starting reference) or negative (which means to the left of the starting reference).
Height is Optional. The height, in a number of rows, that you want the returned reference to be. Height must be a positive number.

Width is   Optional. The width, in a number of columns, that you want the returned reference to be. The width must be a positive number.
COUNTA function also needs to name range so dynamic….
The above two arguments represent height and width generated on the fly by using COUNTA, which makes the resulting reference dynamic.

The COUNTA function counts cells containing any type of information, including error values and empty text (""). For example, if the range contains a formula that returns an empty string, the COUNTA function counts that value. The COUNTA function does not count empty cells.

(2) Table range selected with a defined name.

A dynamic name range is also created simply by selecting data in a worksheet with a table range that is also automatically created a named range and new data when input this range also expands.

Example:

First, select your desired cell or reference then using the shortcut key (ctrl with T) then create a table that automatically creates a dynamic name range like the following image:

When you input any data in the table name range data automatically expands new data.

The above two dynamic name range helps expand automatically new input data for the effect of creating any summary or other related reporting in excel.

You can download the workbook to practice this exercise.. click here

For details learn to see the video tutorials step by step on YouTube….

If this tutorial is helpful for you, comment, share and subscribe to my YouTube Channel for more views.