Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

Add or Edit Modeled Sheet Columns

Explains how to add or edit the columns visible in modeled sheets.

The columns and levels of a modeled sheet make up the interface for your team to enter the data that drives the account values.

Each type of column has a different function:

  • Data entry columns let users to enter data into cells when they open the sheet. Each data entry column has a code. You can reference this code in the sheet's formula-driven accounts. You cannot reference data entry columns cannot from outside the modeled sheet.
  • Custom dimension and attribute columns place existing custom dimensions and attributes on modeled sheets. Users can use these columns to filter the sheet or as part of their input data.
  • Display columns show read-only values calculated by the sheet.
  • Levels are a column in the canvas by default. Because levels are required, you can't remove them. Choose the levels you want available on the sheet and then manage the  properties.

Before You Begin

Required permission: Model Management Access > Model.

See Build Modeled Sheets

How You Get There

Compass.png  From the nav menu, select Modeling. Select Level-Assigned Sheets or User-Assigned Sheets. From the list, select a modeled sheet and then select Columns and Levels from the Sheet Summary. 

How Columns Work in Modeled Sheets

1 Elements: Drag anything from the Elements tab to add a column in the sheet. 

2 Sheet Canvas: Lists the sheet columns. Each row in the canvas is a column in the sheet. The first row represents the first column and so on. 

3 Properties: Controls the appearance and function of the selected column. See Modeled Sheet Column Properties

BuildModeledSheet.png

Look at the type to see what kind of column each row represents:

Type Column in the Canvas.png

Add Columns 

  1. From the Elements pane, expand the categories.
  2. Drag and drop elements into the canvas.
  3. From the toolbar, use the arrows  to arrange the order of the columns. The top row in the canvas is the first column in the completed sheet and so on.  
  4. Define the properties. See Modeled Sheet Column Settings for a comprehensive list describing all settings.
    1. Enter a Name for the column. This appears as the column header in the sheet.
    2. Enter a Code. You can reference this code in the formulas and spreads of calculated accounts. 
    3. Select the Read only checkbox to make the cells of the column non-editable. 
  5. Select Save from canvas the toolbar.

Remove Columns 

When you remove columns you delete all the data associated with that column.

  1. Right-click on a row in the canvas. 
  2. Select Delete
  3. Select Save from canvas the toolbar.

Edit Column Settings

  1. Select a row from the canvas to review the properties. 
  2. Make your changes. 
  3. Select Save from the canvas toolbar.

See Modeled Sheet Column Properties.

Types of Columns

Use the table to understand and choose the columns.

Data Entry Columns

Column Description Calculations When Referenced in Formula
Text  Allows the user to enter numbers or words. Maximum of  512 characters. Text is treated as a literal string.  No calculations are performed on text, even if the user enters numbers or a formula.
Number 

Allows the user to enter a single number into the cell of the column. Users can't enter formulas.

Number elements have special properties to allow the sheet author to specify how the column displays values. See Modeled Sheet Column Properties.

Returns the numeric value entered.

Returns zero if the field is blank.

Date  Allows the user to enter or select a date from a calendar picker, even if it's outside the range of the current version. Dates always consist of a month, day, and year.  Format: mm/dd/yyyy. Value is null if a user leaves a Date Element cell blank.
Text Selector 

Creates a drop-down in the column cells to allow the user to pick from a list. Use the column's properties to create the options available in the list. The first option is the default.   

You cannot delete a text selector choice if it is in use.

You can allow users to add to the list or edit the list from the sheet itself.  See Modeled Sheet Column Properties.

Text is treated as a literal string.  No calculations are performed on text, even if the user enters numbers or a formula.
Initial Balance

Allows users to enter an initial balance for a cumulative plan-by-delta account on the sheet. When you add this column and save the sheet, you automatically create the associated account. It  has the same code you entered for the column.

From the properties, select Account Details to edit the associated account and to add the required formula. The formula calculates the account's delta for all subsequent months after the user supplies the initial balance.

A common use case for this element is a Payrate column on a personnel sheet, which drives the Salary account.

Returns zero if blank.

Because the initial balance is an account, you can reference it by code in formulas outside the modeled sheet.

Checkbox Element

Only available with Adaptive Insights for Sales

Allows the user to select a  checkbox or toggle switch.  Use these with spread lookups to drive data.

For more information on modeled sheet checkboxes , see Use Modeled Sheet Checkboxes With Formulas

If users select the checkbox or turn on the switch, returns a 1. 

If users leave the checkbox unselected or turn off the switch, returns a zero. 

For imported data and formula evaluations, any number other than  zero (e.g. -1, .04, 10) gets treated as 1.

Timespan 

Allows users to enter data into time periods. When you add this column and save the sheet, you automatically create the associated account. It  has the same code you entered for the column.

Modeled sheets may have only one timespan column. You must place it as the last row in the canvas so that it appears as the last column.  The sheet includes a single time grid for each row. Users can enter numbers or formulas in the cells.

From the settings, select Account Details to edit the associated account.

Returns zero if blank.

Because the timespan is an account, you can reference it by code in formulas outside the modeled sheet.

Dimensions

Column Description Calculations When Referenced in Formula
List of your instance's custom dimension Allows the user to select the dimension values from a drop-down.  Returns zero if blank.

Attributes

   
Column Description Calculations When Referenced in Formula
List of your instance's attributes Allows user to select the dimension values from a drop-down. Returns zero if blank.

Display Columns

   
Column Description Calculations When Referenced in Formula
Level Currency Allows users to view the currency for the selected level. Users select the level from the cell drop-downs in the level column.  Not evaluated.
List of the modeled sheet's accounts Allows users to view the calculations of the accounts driven by the data they enter in the sheet. Not evaluated.

 

  • Was this article helpful?