Latest posts by techwriter (see all)
- 101 Tips and Tutorials to Write Like a Pro - August 17, 2017
- How to Find a Technical Writing Job – Some Ideas and Resources - August 9, 2017
- BOOK REVIEW: “Design for How People Learn” by Julie Dirksen - July 10, 2017
© 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.