Excel Define Dynamic Named Range

Excel Dynamic Named Range

        Dynamic named range has advantages over normal named range. For example ,the dynamic named range expands automatically when user added a value to the range.

To define dynamic named range ;
➤ On the Formula tab, in the Defined Names group,  Define Name is clicked. Or, Ctrl + F3 is pressed  to open the Excel Name Manager, and  the New button is clicked.
➤ New Name dialogue box is opened.
➤ In the Name box, name of for dynamic range is wrote. We entered Product_Prices name.
➤ In the Scope dropdown, Workbook is default value.
➤ In the Refers to box,  OFFSET and COUNTA formula is entered.

Our formula for Product_Prices name :  =OFFSET(Products!$B$2,0,0,COUNTA(Products!$B:$B);1)

The generic formula to create a dynamic named range in Excel is as follows:

= OFFSET(sheet_name!first_cell, 0, 0, COUNTA(column), 1)

        If user are defining a dynamic range in the current worksheet, user do not need to include the worksheet name in the references, Excel will do it automatically. If user are creating a range for some other sheet, first ; the sheet name, then the exclamation mark and cell or range reference must be entered.

As an example, we have specified the sum of cells in the field we defined with the name Product_Prices in cell E3 :

     

         Excel OFFSET function takes 5 arguments.
➥ Reference: $B$2,
➥ Rows to offset: 0,
➥ Columns to offset: 0,
➥ Height: COUNTA($B:$B)
➥ Width is equal to 1 column.
COUNTA($B:$B) counts the number of cell values in column B that are not empty. When added a new value to the range, COUNTA($B:$B) increases. Thus, the range returned by the OFFSET function enlarges.

To understand the working logic of the dynamic named range, let's make some changes to the sample template: ↴

As seen in the animation above ; when a value is added or subtracted to the range, Excel updates the sum value as automatically.

Dynamic named range can be defined for multiple columns. For example, we have defined the column A and column F the dynamic named field with the following formula :

 =OFFSET(Company!$A$2,0,0,COUNTA(Company!$A:$A),6)

Since the formula contains 6 columns, the number 6 was used as the width.

Let's make an example of get data from Company sheet to Invoice sheet using formula ;
- First, let's create a dynamic data validation list in cell B2 in Invoice .For this, we have defined the Companies name.

Companies =OFFSET(Company!$A$2;0;0;COUNTA(Company!$A:$A)-1)

While cell B2 is selected, = Companies is entered into the Source section of the Data _ Data Validation _ List menu.

- Let's create a dynamic named range that covers multiple columns.

  Company_List =OFFSET(Company!$A$2,0,0,COUNTA(Company!$A:$A),6)

Let's enter the necessary formulas into cells B3, B4, B5, B6 and B7.

  For B3 cell : =IF(ISERROR(VLOOKUP(B2,Company_List,2,FALSE)),"",VLOOKUP(B2,Company_List,2,FALSE))
  For B4 cell : =IF(ISERROR(VLOOKUP(B2,Company_List,3,FALSE)),"",VLOOKUP(B2,Company_List,3,FALSE))
  For B5 cell : =IF(ISERROR(VLOOKUP(B2,Company_List,4,FALSE)),"",VLOOKUP(B2,Company_List,4,FALSE))
  For B6 cell : =IF(ISERROR(VLOOKUP(B2,Company_List,5,FALSE)),"",VLOOKUP(B2,Company_List,5,FALSE))
  For B7 cell : =IF(ISERROR(VLOOKUP(B2,Company_List,6,FALSE)),"",VLOOKUP(B2,Company_List,6,FALSE))