Spreadsheets

Spreadsheet in a file

A spreadsheet frame works and acts exactly like a spreadsheet, but with Blockpad formula features.

Plus, you can reference document values from a spreadsheet, so your calculations can be connected.

Insert a spreadsheet in a file:
  1. Click the insert button.
  2. Select spreadsheet.
  3. Click OK.

For the basics, you can have tables of text and numbers.

You can have cells with formulas for math calculations, and use normal spreadsheet functions inside of them.

Sort and filter

You can sort and filter ranges in a spreadsheet. Learn more in the links to the Blockpad website below.

Conditional formatting

You can also conditionally format cells using Blockpad Style Rules.

Blockpad specific features

Units

Blockpad spreadsheet formulas have all the features of dynamic equations described in Calculations. So, you can do things like math with units.

Named cells

You can use a formula to assign a name to a cell, then use that name to reference it.

Custom functions

You can create your own functions and use them in the spreadsheet.

Reference document values

You can also reference values from other locations in the file.

Multiline cells

You can change a cell to be multiline, where you can do anything that can be done in a report. See multiline cells in Calculations to learn how.

Show formula

In spreadsheets some things are not displayed in a formula after entering. In a spreadsheet cell, the formula is automatically hidden. Only the result and the assigned name are shown. If there is not an assigned name, then only the result is shown.

You can show and hide formulas and results, just like in dynamic equations.

Control cell formula visibility:
  1. Select a cell with a formula.
  2. Open the properties window.
  3. Near the bottom, under Formula, toggle Show Formula, Show Name, or Show Result.

Table regions

You can specify groups of cells as table regions to make looking up values cleaner. After you've set up a table region, use the name of that region as a function to lookup values from that table.

Specify a table region:
  1. Select a range of cells
    • The first row of these cells should be headers, and the columns should be values that correspond to the headers.
    • The first column will be the "lookup column".
  2. In the toolbar, select Table>Define Region
Name the table region:
  1. Hover over the region and select the tag.
  2. Open the properties window.
  3. Change the Name property.

To use the table region in a formula, type in the region name, open parentheses, the lookup value (as text if it's a text value), and the header as text. For example: Region1("Steel", "Modulus of Elasticity").

As a matter of practice, if the table region is inside of a table, you may want to set Capture Values to "No" for the table, so you don't have to reference the table name first. This was done in the example below.

Under the hood, table regions are treated as an array of key-value objects. Because of this, you can use functions meant for arrays to get information from table regions.

Open a CSV

You can open a .csv file as a spreadsheet in Blockpad, then use it for your calculations.

Open a .csv file as a spreadsheet:
  1. Select File>Open in the toolbar.
  2. In the bottom right of the window that appears, change "All supported files (*bpad, ...)" to "CSV (comma-delimited) (*.csv)".
  3. Navigate to file you wish to open, select it, and click Open.
  4. Click the Edit as Blockpad file button.
  5. Treat the spreadsheet contents like any other Blockpad spreadsheet content.

Often old csv files have numbers with no units assigned. For a quick way to assign units to these tables, see the section about it in the scripts deep dive.

Mini-spreadsheets in a document

Blockpad also supports mini-spreadsheet tables inside of a document. See the section on tables in calculations for more information.

 

Next - Reports