This example demonstrates:
| ® | Setting up a Products and Services item to provide the text that will trigger a subtotal calculation |
| ® | Using the item on a QuickBooks Online form, to control where subtotals occur |
| ® | Entering a macro worksheet formula to subtotal a column |
| ® | Connecting the Products and Services item to the subtotal formula |
 | This technique works on any QuickBooks Online form which has an Amount column. |
Required reading... This discussion assumes you've read the Column subtotals and totals (QBOL) topic, so please read that topic first if you have not done so already. |
Prior topics showed how to subtotal a column of numbers—the Qty (quantity) column, for example. The Amount column must be subtotaled a bit differently.
QuickBooks Online requires assigning an account to items you create in the Products and Services list. So when you use an item to trigger a subtotal calculation on a form, you must be careful that you don't accidentally post income. That's what would happen if the macro wrote the subtotal in the Amount column!
The solution is to put the subtotal in the Description column, where QuickBooks Online won't treat it as a financial amount and thus won't post the subtotal amount as income.
Getting Ready
Instead of using existing Products and Services items to trigger FullSpeed calculations, usually you should set up a new item for that purpose. This assures the item won't record income unless you want it to (if the Price/Rate is set to 0) and it lets the item provide any descriptive information you may want.
Here's a new item, Subtotal, added for the purpose of triggering a subtotal calculation:
Notes:
| ® | The item Name can be anything you want. |
| ® | We've left the Description blank, for less clutter on QuickBooks Online forms. But you can enter a description if you want. |
| ® | The Accounts you select don't matter much because the Price/Rate is set to 0, so no income or expense should be recorded when this item is used. |
Use the item on a form
Select the Subtotal item on the line immediately below each group of lines you want to subtotal.
Set up the macro worksheet as shown below
| 1. | Enter a formula to subtotal the Amount, on a text-triggered formula row. |
As the screenshot below shows, the formula sums the Amount column but is entered in the Description column for the reasons given at the beginning of this topic.
| 2. | Enter the trigger text on the same row. |
| 3. | Choose macro options appropriate for the target application, QuickBooks Online. |
| 4. | Be sure to mark the Begin field and the Match column. |
Here's an example with some sample data entered in the Amount column, to verify formula's results:
Notice that the formula actually sums the Sandbox rows of the Amount column. That is a shorthand way of telling FullSpeed to sum the entire Amount column when the macro runs.
| The easy way to enter the formula would be as: =SUM(G0:G0). FullSpeed converts row 0 references to Sandbox row references, so you don't need to know what they are. |
Format the subtotal formula cell as currency
The "23" calculated by the formula is correct, but when the macro runs it will need to be formatted as dollars and cents (currency). Right-click the formula's cell and select Format cell from the menu.
Then select currency formatting with two decimal places:
| Remember to save the FullSpeed file, to avoid losing your work! |
Running the Macro
In QuickBooks Online:
| 1. | Click on the first cell in the Qty column. |
That's the column selected as the Begin field (shown in bright green in the macro worksheet above), and the macro needs to start on the first row of data.
| 2. | Press the hotkey assigned to the macro, Shift+Ctrl+3 in this example. |
FullSpeed will traverse the form, gathering data. As it reaches each Subtotal line it will put a subtotal in the Description field:
