Was this page helpful?
Thank you!

Comments or suggestions?



Enter Email Address (optional)
email

Add a subtotal field in Quickbooks Statement Writer

When you try to add a subtotal within a cell in Quickbooks Statement Writer* (QSW), the action is not allowed.

Excel returns this message:

The cell or chart you are trying to change is protected and therefore read-only.

All cells are locked and password protected in QSW, so they can only be changed by the integrated module. However, it is possible to create a report with as many customizations as necessary.


*  QuickBooks Statement Writer (QSW) was referred to as Intuit Statement Writer (ISW) in earlier versions of Quickbooks.
Detailed instructions

There are two ways to add a subtotal: by adding a row or by adding a column.

Option 1: Add a row

When you insert a row within the protected area, the protection is not applied to the inserted line. This allows you to enter text or create formulas within the protected area.

  1. Right-click the row where the subtotal or other formula should be entered and choose Insert.
  2. Enter your formula in the appropriate cell. The format will depend on your version of Excel.

Note:  If you need help with formulas, review examples for MS Excel

 

Option 2: Insert a column

The following steps create an entire unprotected column and map data into that column.

  1. Open QuickBooks Statement Writer and the statement you want to customize.
  2. Right-click the letter heading of the column you want to change and choose Insert.
  3. Use Excel to create a formula that makes the entire column equal the column you wish to modify:
    1. Click in the first cell of the blank column next to the first cell in the original column that has information (such as the year).
    2. Press =.
    3. Click the exact cell in the column you wish to modify or copy and press Enter.
    4. Drag the small square box on the formula cell down the column beyond the last data rows. This may need to be done for each subsection of the report.
  4. Create a Subtotal as normal in Excel. You may need to insert a new row.
  5. Formatting can be easily copied from one column to the next by using the Format Painter on each section. See MS Excel Help.
  6. Some formatting cleanup may be needed, such as removing extraneous zeros in blank cells.
  7. Right-click the column with the original information from QuickBooks and choose Hide.
  8. The new column will continue to refresh with data from QuickBooks because its data is drawn directly from QuickBooks through the created formula.

    Caution: Any changes made to this column and the cells populated with QuickBooks data can change the formula's results. Be sure to keep the first formula intact.

KB ID# HOW13402
12/8/2016 12:09:06 PM
QYPPRDQBKSWS09 9138 Pro 2017 8a53ec