Aha! Roadmaps | Custom worksheets (Enterprise+)

A custom worksheet is a special type of custom field. Think of it like a spreadsheet. You can create as many rows as you need and configure them to calculate your data based on formulas and functions. Worksheet formulas can reference other cells in the worksheet and calculate them using a wide range of functions and operations.

To create, modify, and delete custom worksheet fields, you must be an administrator with customization permissions, though owners and contributors can enter data in worksheet fields.

Custom worksheet fields, just like our other custom fields, are added to layouts that are assigned to workspaces. To add a new custom worksheet field:

1. Navigate to Settings ⚙️ Account Custom fields.

2. Choose a record type where you would like your custom field to appear. (e.g. initiatives, releases, features, etc.)

3. Click the Add custom field button and choose Worksheet.

4. Add a Name for your worksheet.

5. Click the + Add row button. (To remove a row, right click on a row and choose Delete row.) Worksheet rows consist of three columns: Type, Field name, and Value.

   1. Type determines how the value is formatted in the worksheet. Possible types are Text, Number, Percent, Currency, Date, or Boolean. Consider what kind of value you want to display and select the appropriate type.

   2. Field name is simply a label for the value. However, it is important to note that this name will be used in value equations and reporting. Updating the name of this field will not update the equations that reference the field.

   3. Value can be blank, predefined, or an equation. Blank values can be completed later when viewing the worksheet on a record. Predefined values cannot be edited on records, but they can be referenced in equations. Equations can reference any other worksheet field and present calculated values based on custom formulae. Advanced equations can reference fields on records linked to the custom worksheet's record using a special field identifier syntax.

6. The type of value for each row in your worksheet can be changed by clicking on the icons that appear in the Type column. See table below.

   

7. To add an equation to your worksheet, begin typing in the Value column. This will launch the Equation builder. Add fields to your equation by clicking them. Include operators between your fields for simple calculations (addition +, subtraction - , multiplication * , or division / ). The equation builder also includes a number of more complex functions that can be used to calculate your field value. Hover over each function to see syntax and examples. Once you have written your equation, click Save.

   

   Note: When you are searching for a field, function, or operator, you can select a result by moving up or down with the arrow keys. You can add your selection using the Return/Enter key. For more information, see a complete list of functions and complete list of fields. Some advanced equation fields allow you to filter arrays with logical operators. You can also start with an introduction to advanced equations.

8. To add a fixed variable to your worksheet, type the variable (e.g. 400, 1/1/2019, TRUE, etc.) in the Value cell.

9. To add a section heading to your worksheet, click Add heading and drag it to a location within your worksheet. You can name the heading by clicking the section heading text.

10. Once you have created your worksheet, you can choose to add it to a workspace or use it in an existing layout.

Custom worksheets must belong to at least one custom field layout to be accessible by other users.

The six data types supported by custom worksheets include:

Custom worksheets are supported by custom tables, and can reference custom table records. To add a custom worksheet to a custom table, navigate to Settings ⚙️ Account Custom tables, select a custom table, then follow the steps above, starting at step 3.

Top

Use custom worksheet fields

Once you have created your custom worksheet and added it to a custom layout, you are ready to put it to use. The worksheet will trigger for any records created after you added it to the custom layout.

• If you want to use custom worksheets to collect data manually, click into a worksheet row to begin adding values. Any equations that depend on your values will execute in real time as you populate your worksheet.

• If you have included an advanced equation in your custom worksheet, the equation will begin calculating immediately and will refresh automatically as data changes.

Custom worksheet equations will refresh automatically if there are fewer than 200 dependencies. For example, if your equation starts from a feature, then calculates the remaining capacity in the feature's release by looking at all the other features in the release, that equation will stay up to date until there are more than 200 features in the release.

Equations with more than 200 dependencies will not break — you will just need to manually refresh the worksheet by clicking the Refresh button.

Hover over a calculated field to see the calculation. You cannot add data to fields that hold an equation.

Worksheets are greyed out until data is added to at least one field on the worksheet. Any fixed variables that you may have added to your worksheet will carry a blank value until data has been added to the worksheet. If you change your equation after data has been calculated on a custom worksheet, the worksheet will recalculate with the updated equation.

You can import custom worksheet data from a CSV file. Each worksheet sub-field will appear as a custom field to be imported.

Top

Report on custom worksheet fields

Values that you have added to worksheets can be added to new or existing reports. To report on custom worksheets with a new list report:

1. Create a list report and then add custom worksheet fields by clicking the Add data button.

2. Choose the record type where your worksheet(s) are and then click Add column.

3. Your worksheet values will appear in the Custom fields section of the dropdown, prefixed with the name of your custom worksheet.

Top