Latest posts by techwriter (see all)
- Test Your Knowledge of 4 Basic Fonts – Drag & Drop - January 27, 2017
- How NOT to Design a Web Site - January 25, 2017
- Hazards of Poorly Written Technical Documentation - December 26, 2016
© Ugur Akinci
Let’s say you have a data table like this:
PROBLEM: We want to create a SUM for the GROSS column that would adjust itself dynamically when new sales records are added to the table.
How would you do that?
STEP 1) NAME the range dynamically by using the OFFSET function.
STEP 2) Use that NAME in your SUM formula.
- Select the GROSS column.
- Click Ctrl+F3 to display the NAME MANAGER.
- Click New to create a new name.
- Enter the following formula for the new range name GROSS:= OFFSET(Sheet1!$B$1,1,0,COUNTA($B:$B)) NOTE: If you are not using “Sheet1″ but another worksheet, please use the appropriate worksheet number or name in the formula.
- Click Close.
Define your SUM GROSS (cell E2) by entering the following formula in cell E2:
The sum of all GROSS sales is calculated automatically:
TEST: Now let’s insert two new records and see if the SUM GROSS is updated dynamically (automatically):
RESULT: Yes, the SUM GROSS is adjusted automatically.