Question about creating dynamic named ranges in Excel model based on user input

#1

I would like to know how to update the named ranges for Excel model variables based on user decisions. I want to let the users choose how long the simulation will run for (between 5 to 10 years/time steps) and need to display a target value on some line charts that spans the entire duration of the game.

To do this, I’ve created a model variable (NumYears) which is a single cell that can be set by the user.
Based on this value, the ‘Time’ model variable’s named range will change dynamically. (0 to NumYears years)

I’m able to update the ‘Time’ variable’s named range based on the value of ‘NumYears’ using either the OFFSET or INDEX excel functions in the name definitions.

This works as expected if I directly set the value of ‘NumYears’ in the excel sheet, but doesn’t work if the value of ‘NumYears’ is set by the user through the simulation interface.

Is there any way I can make this work?
Regards, Tim

#2

Hi Tim,

We discussed over email, but let me include the response here as well.

You were trying to make the length of the graphs dynamically determined by a model variable. I experimented with this, and the only way I could make this work is to manually edit the HTML.

I changed this:

<contour-chart data-f-categories="Time" data-ib-skip-timevariable="true" hide-series-not-in-color-map="" class="hide-legend" data-f-source="REVPAR_Final[0..<Step>]" x-axis-title="Year" y-axis-title="$" enable-markers="true" series-labels="{&quot;REVPAR_Final[0..<Step>]&quot;:&quot;REVPAR ($)&quot;}">

to this:

<contour-chart data-f-categories="Time[1..<NumYears>]" data-ib-skip-timevariable="true" hide-series-not-in-color-map="" class="hide-legend" data-f-source="REVPAR_Final[0..<Step>]" x-axis-title="Year" y-axis-title="$" enable-markers="true" series-labels="{&quot;REVPAR_Final[0..<Step>]&quot;:&quot;REVPAR ($)&quot;}">

Note the change:
data-f-categories="Time[1..<NumYears>]"

This makes the X axis run from 1 to NumYears. Note this won’t be redrawn if it changes in the middle of a game. But if the facilitator sets this up, and then players log in, it appears to work.

WILL

#3

Thank you Will. We applied this and it worked for us.
Regards, Tim