Automating the Spreadsheet using Check Boxes, DSUM & IF

In this post, we put this altogether with an interactive report. Using check boxes and DSUM the user will select what criteria to sum by.

Advertisements

4 of 4: Automating the Spreadsheet using Check Boxes , If Statements, and DSUM

In the previous posts we added Check Boxes to our spreadsheet, linked Check Boxes to cells, and created a conditional summation using DSUM. In this post, we put this altogether with an interactive report. Using Check Boxes, IF statements, and DSUM, the user will select what criteria to sum by.
Dsum_CheckBox
Click to enlarge

In the example depicted above, we sum the values in the Amount column based upon the criteria entered in column G.

Watch this ~3m video to learn more.

 

Adding and Linking the Check Boxes

DSUM_Checkbox Properties Linked Cell

  1. Insert two blank rows above the database.
  2. In the second blank row, add Check Box Form controls for each criteria value to be included in the summary. In the example depicted, we created Check Boxes for each region (see Adding a Check Box for instructions).
  3. Link the Check Box to the cell above: Right + Click on the Check Box, select Format Control, and enter above cell in the Cell link field (for more detailed instructions click here ). Repeat for each Check Box.

 

Creating the Criteria Column

DSUM_Checkbox Function dialog

We will use the IF function to copy the Checked labels into the criteria range

  1. In a blank column, enter the field name into the cell (i.e., ‘Region’) .
  2. Move cursor to the cell immediately below.
  3. On the Formula tab, in the Function Library group, click Logical and select IF.
  4. In the  Logical_test field, select or type Linked cell above the Check Box. In the example depicted below enter A3 to test Checked state for ‘North America.’
  5. In the Value_if_true field, select or type the Check Box cell. In the example depicted below enter A4 for the text ‘North America.’
  6. In the Value_if_false field, type ” – ” (dash in quotes).
  7. Click OK.

Repeat steps 2 through 7 for each Check Box value to be included in the summation.

To  test the IF statements, check and uncheck the boxes. When checked, the text associated with that Check Box will appear in the criteria column.

 

Inserting the DSUM function

DSUM_Checkbox Properties DSUM

  1. Select the cell to contain summation (i.e., F5).
  2. On the Formulas tab, in the Function Library group, click Insert Function. The Insert Function dialog appears.
  3. In the Search for function area, type DSUM and click Go. DSUM appears in the Selection function area.
  4. Click OK. The Function Arguments dialog appears.
  5. In the Database area, enter either the saved name or cell reference for the table (e.g., FoodInventory or A7:F255).
  6. In the Field area type the column heading to summarize, in quotes (i.e., “Amount”).
  7. In the Criteria area, type or select the cells that contain the criteria; this includes the column heading(s) and value to search for (i.e., G4:G8).
  8. Press OK.

Tip: Hide the criteria column to make the report appear less cluttered.

That’s it. Now, to add a region to the summation, check that region’s Check Box. To remove a region from the summation, uncheck it.

 

Cheers!

hɔuᴉnb

 

Previous post and additional reading:

Comments and questions are always welcome!

Author: quincy harley jr

Quincy is a technophile, coach and Learning Development expert. He has an extensive legal IT background and is practiced in MS Office application support and product development. As a project leader he has been integral in numerous new application rollouts. Whatever spare time he has, is spent with reading, archery and watching his young sons grow.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s