Dynamic named ranges in Excel – automatic updating calculations: Generally a user inputs data into the Excel spreadsheets according to a pre-thoughtout plan. Then he performs calculations on the data as required. Later he adds more data into the worksheet and has to rerun the calculation process. You can now create dynamic named ranges in such a manner using the offset and ‘counta’ functions with your named ranges that the complete process of recalculations or search is automated. We have input, for example, a few names of employees and their salaries. Then we define a ‘named’ range called ‘mydata’. We then perform a ‘vlookup’ function on the salaries data in the Excel worksheet. Then we enter the name of another employee and his salary in the usual fashion and we note that we cannot perform the Vlookup again to find the salary of the new employee. We can now redefine the named range mydata using the offset and ‘counta’ functions, as shown in the Excel training video, in such a way that the named range now becomes a dynamic named range.
When we now add another employee’s data the Vlookup of the salaries of the new employees is done instantly and automatically. This is definitely a great advantage as compared to updating the Excel data manually!
How to create a dynamic defined range in an Excel worksheet