named ranges - 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
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
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!