Latest posts by techwriter (see all)
- 3 Ways to Add Copyright Free Images to Your Blogs, Books and Documents - September 19, 2016
- How to Delete All Hyperlinks in a MS #Word Document through VBA Macro - September 1, 2016
- How to View a List of All Open MS Word Documents through VBA Macro - August 31, 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.