|
Dynamic
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 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 'sum' 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 have to perform the calculation again to
arrive at the total of the new salaries. 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 sum of
the salaries is updated instantly and automatically. In fact, when we
enter the ;HRA' or the 'House Rent Allownace' into the spreadsheet the
data sum is automatically updated. This is definitely a great advantage
as compared to updating the Excel data manually! |
|
|
|