(revised and expanded from an earlier post)
In the previous posts we added Check Boxes to our spreadsheet and linked those Check Boxes to cells to display True when checked and False when unchecked. In this post, we take a brief break from Check Boxes to explore the DSUM function. But don't fret, in upcoming post we will tie Check Boxes and DSUM together to produce an interactive report.
DSUM, short for Database Summation, enables you to summarize data in a table (aka, database) based upon a specific condition (aka, criteria). Similar to the SUMIF function, DSUM can employ Boolean (i.e., AND/OR) logic in its criteria.
=DSUM( database, field, criteria)
Database: the entire table, including row values, and headings
Field: the column containing numbers to sum
Criteria: condition to test for inclusion in the summary (contains field headings and value)
In the example depicted above, we sum the values in the Amount column based upon the criteria entered in B3:B4 (i.e., Region: Europe)
Watch this ~3m video to learn more.
Tip: when working with large data, name the cells to simplify formula entry.
To Name a Group of Cells
Select all the cells. Tip: select one cell, then press CTRL + A to select all contiguous (touching) cells
Click in the Name box, type name (e.g., “FoodInventory”) and press Enter. Note: Names cannot begin with a number, or include spaces.
Creating the Summation using DSUM
Type criteria, entering the field names on one row and the condition to test right below it
(i.e., In B3 enter Region and in B4 enter Europe).
Select the cell to contain summation (i.e., A4).
On the Formulas tab, in the Function Library group, click Insert Function. The Insert Function dialog appears.
In the Search for function area, type DSUM and click Go. DSUM appears in the Selection function area.
Click OK. The Function Arguments dialog appears.
In the Database area type the name of database (i.e., FoodInventory). Tip: press F3 key to display list of named cells and select from that list.
In the Field area type the column heading to summarize, in quotes (i.e., “Amount”).
Alternatively, you can enter the number of the summation column, counting from left of the table (i.e., 5).
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., B3:B4).
Next and Final: Automating the spreadsheet using DSUM and Check Boxes.
In the earlier post, Adding Check Boxes, we added the Form Check Box control to our spreadsheet. In this post we explore adding an ActiveX Check Box to the spreadsheet, and how to link a Check Box to a cell. The linked cell will display True, when the Check Box is checked, and False when unchecked.
Form Check Box
Of the two types of Check Box controls, Form and ActiveX, the Form control is the simpler of the two types.
Form Check Box features:
simple management, with comparatively few options
compatible with the XLS file format (Excel 2003 and earlier)
Watch this ~3m video to see how to place the Form Check Box and link it to a cell.
To Link the Check Box Form Control to a Cell
Add the Form Check Box control (for details, see earlier post or review the above video).
Right + Click on the Check Box control and select Format Control. The Format Control dialog appears.
On the Control tab, click in the Cell link field, then type or select the cell.
ActiveX Check Box
For more programming power, use the ActiveX Check Box. Similar to Form Check Boxes, the ActiveX Check Box has additional options.
ActiveX Check Box features:
additional formatting and programming options
designed to be associated with macros
compatible with the XLSM and XLTM file formats (macro enabled file and macro enabled template, respectively)
Watch this ~3m video to see how to place the ActiveX Check Box and link it to a cell.
To Link the Check Box ActiveX Control to a Cell
On the Developer Tab, In the Control group, Click the ActiveX Check Box control, then Click + Drag to place on spreadsheet. Note: the spreadsheet is automatically placed in Design Mode.
Right + Click on the Check Box control and select Properties. The Properties dialog appears.
Click in the Caption property field and delete the text (i.e., CheckBox1).
For the BackStyle property, click drop-down and select 0 – fmBackStyleTransparent.
Click in the LinkedCell property field and type the cell reference (i.e. F10).
Close the Property dialog.
On the Developer tab, click Design Mode to toggle Design Mode off.
In these four post we will will create an interactive report using Check boxes and DSUM (and without the using macros). In this first of four, we start with adding Check Boxes.
Since Excel 2007, the use of macros in spreadsheets have added an additional element of complexity. Macros must now be stored in XLSM format, which raises a red flag in excel as a potential threat. In this series of posts, we eschew VBA and ActiveX automation, in exchange for ‘old fashioned’ excel programming, using form controls and statistical functions.
Adding Check boxes to your spreadsheet can make it easier for you and your reviewers to edit spreadsheet data. The Check box tool is found on the Developer Tab. To add the Developer tab, click File, Options, Customize Ribbon, and check Developer.
To Add a Check box
On the Developer tab, in the Controls group, click Insert Check box (Form Control).
Using the mouse, Click & Drag to draw a Check box control. A Check box control is placed, consisting of the Check box and the label text. Tip: Hold down the ALT key as you Click & Drag to draw an object equal in size to the selected cell.
Right + Click on the Check box control and select Edit Text. An insertion point appears.
Delete the Check box label text (e.g. Check Box 1).
Click & Drag rightmost selection handle to reduce the size of the Check box control.
Enter your text into the cell that contains the Check box. Tip: To indent the text, on the Home ribbon, click the Alignment dialog launcher and increase the cell Indent to 2.
The Activité, by Withings, is a fashionable analog device (designed in France, and built in Switzerland), that leverages a retro design, while offering the activity and gps-enabled features of wearble-tech gadgets. And it looks smart, without looking like a smartwatch.
If you thought Wargames was cool (back in the day) you’ll love this interactive map of Cyberattacks. Click the graphic to view iteractive imaging. Though engaging to watch, I can’t vouch for the credibility; Just why is Iceland attacking St Louis, anyhow?
Want to show page count in a multi-section document?
On the distant heels of my renumbering slides post, here are instructions to create Page x of y style numbering in a multiple section document. In a simpler document (one without section breaks), you could easily create the ‘Page 3 of 7’ type numbering using the NumPages field. NumPages, however, counts total number of pages in the document and not in each section.
‘SectionPages,’ to the rescue!
Insert Page x of y Numbering in a Multiple Section Document.
On the Insert tab, click Footer and select Edit Footer. The cursor will move to the footer of the current section.
Position your cursor where you want to place the page number.
On the Header & Footer Tools: Design tab, in the Header & Footer group, click Page Number, Current Position, and select Plain Number. The page number field is inserted, reflecting the current page number.*
Type ‘ of ‘ (no quotes) after the page number.
On the Header & Footer Tools: Design tab, in the Insert group, click Quick Parts, and select Field.
Select Numbering in theCategoriesdrop-down and SectionPages from the Field names area, then click OK. The SectionPages field is added to the footer using the default numeric format.
*Restart numbering , if necessary: Click Page Number, select Format Page Number, and change Page Numbering to Start at: 1.
Try this: Suppress the number on your first (title) slide, and renumber the second slide Slide 1. This makes referring to talking points easier since it is unlikely there is anything you will refer to on the title slide.
Follow these steps or view this 30 second video.
On the Insert tab, in the Text group, click Slide Number. The Header and Footer dialog appears.
Check Slide number and Don’t show on title slide check boxes.
Click Apply to all.
On the Design tab, in Customize group, click Slide Size and choose Custom Slide Size. The Slide Size dialog appears.
(PowerPoint 2003/07 users: Click Page Setup to display the Page Setup dialog)