Skip to main content
Documentation
Documentation
Adaptive Planning
Last Updated: 2023-06-23
Add Value Lookups to Modeled Sheets

Add Value Lookups to Modeled Sheets

A value lookup is an optional modeled sheet function. You assign values to the options that users select in the sheet. The value calculates and populates in another account on the same sheet. You can add value lookups to
Dimension
 columns or
Text Selector
 columns. For dimensions the options are the available dimension values. For text selectors, you create the options available.
Example: In your Personnel modeled sheet, you track benefits for each employee. You have a text selector, called
Medical Benefits
 and the options are
Family
,
Single
,
None
. You assign values to each option for the year:
200
 for
Single
,
600
 for
Family
, and
0
 for
None
. You then create a calculated account called
Medical Benefit Expense
. The formula references the value lookup. Now, when users select one of the
Medical Benefi
t options, the cost is automatically calculated in the
Medical Benefit Expense
 account through the year.
The benefit of using value lookups is that you can adjust the values through time. Example: Benefit costs increase midway through the year. You can change the values in the value lookup to keep the calculation accurate.
Value lookups require:
  • Modeled sheets.
  • Dimensions
     or
    Text Selector
     columns: You add the value lookup to the column. The dimension values are the options available for dimensions. For
    Text Selector
    , you must create the options. From the sheet view, users select 1 of the options to trigger the value lookup. Example: The text selector,
    Medical Benefit
    , has 3 options:
    Single
    ,
    Family
    , or
    None
    .
  • Values entered into the cells of the value lookup sheet. You get to the value lookup sheet when you edit the value lookup in the settings of the dimension or text selector column in the sheet builder.
  • Calculated accounts: The calculated account holds the value lookups for the row in the modeled sheet.
  1. From the
    Sheet Summary
    , click
    Columns and Levels
    .
  2. From the canvas, highlight the custom dimension or text selector.
  3. In the
    Value Lookups
     section, click
    Add new value lookup
    .
  4. As you complete the task, consider:
    Option Description
    Name
    Enter the name of the value lookup. You reference the value lookup name in the formula of the calculated account.
    Display As
    Select
    Number
     or
    Percent
     to indicate how you want to enter the values in the value lookup sheet.
    Decimal Places
    Select the number of places after the decimal point that you want to be able to enter in the value lookup sheet.
  5. Save the sheet.
    You must save the sheet before you can open the value lookup sheet.
  6. Highlight the text selector column or custom dimension column that has the value lookup.
  7. In the
    Value Lookups
     section, click the
    Edit
     icon next to the value lookup name.  to display the list of dimension values or text selector options.
    This action opens the value lookup sheet. Each dimension value or text selector option displays in the rows. The columns display a timespan.
  8. Enter the values for each row in the cells.
    As a best practice for values that are consistent over time, you can enter the value in the first column and then right-click and select
    Copy Forward
    .
  9. From the sheet toolbar, click
    Save
    .
  10. When you create or edit a calculated account to hold the values:
    For
    Formula
    , refer to the name of the value lookup table using ROW. Example:
    ROW.BenefitLookup
    . This formula says: Look at the option selected in the same row for the column that has the value lookup called
    BenefitLookup
    . Calculated a value based on the values assigned in the lookup table for each time period.
The calculated account populates with the values you assigned in the value lookup table based on the options selected in each row. Calculations on modeled sheets happen in the background. To see these values in the account, you either add the account as a display column to the modeled sheet, or you right-click the row from the sheet view and select
Row Details
. You can also create charts and reports using the account.
To edit value lookups:
  1. Click
    Value Lookups
     from the
    Sheet Summary
     screen.
  2. Click the value lookup to open the sheet.
  3. Change the values on the sheet and save.