Add Value Lookups to Modeled Sheets

Create Custom Dimension Values. (f you're using custom dimensions for the value lookup.)

Add Text Selectors to Modeled Sheets. (If you're using text selectors for the value lookup.)

Start a New Sheet or Get to the Sheet Summary to Edit Sheets.

Security: Model includes sheets, accounts, dimensions, and formulas
permission.

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

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.

From the

Sheet Summary

, click

Columns and Levels

From the canvas, highlight the custom dimension or text selector.

In the

Value Lookups

section, click

Add new value lookup

As you complete the task, consider:

Option	Bezeichnung
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.

Save the sheet.

You must save the sheet before you can open the value lookup sheet.

Highlight the text selector column or custom dimension column that has the value lookup.

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.

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
.

From the sheet toolbar, click

Save

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:

Click

Value Lookups

from the

Sheet Summary

screen.

Click the value lookup to open the sheet.

Change the values on the sheet and save.