Frontline Systems, Excel Solver, optimization software, Solver Excel, simulation software
Solver.com
From Frontline Systems, developers of the Excel Solver.

Solver tutorials

Learn to use optimization for resource allocation, and Monte Carlo simulation for risk analysis of your model.


 

Premium Solver Platform - Multi-Worksheet Models

Multi-Worksheet Models

The Premium Solver requires that cells containing decision variables and constraint left hand sides are on the active worksheet. But the Premium Solver Platform allows you to define decision variables and constraint left hand sides on any worksheet of a workbook. For example, we can split the EXAMPLE1 model into two parts, as shown below.

Worksheet Example1 contains the decision variables, and the coefficients and SUMPRODUCT formula for the objective. Worksheet Inventory contains the constraint left hand sides (formulas), coefficients and right hand sides.  The constraint formulas on worksheet Inventory refer to the variables as

Example1!$D9:$F9.


Multi-worksheet EXAMPLE1 model

Starting on worksheet Example1, we can display the Solver Parameters dialog, click Add or Change, then simply point and click across worksheets to select the constraint left and right hand sides on worksheet Inventory.


Change Constraint dialog

The resulting Solver Parameters dialog is shown to the right.  You could define another, different Solver model on worksheet Inventory, and this model could refer to variable and constraint cells on worksheets Example1 and Inventory. The Premium Solver Platform keeps track of all the models.

Solver Parameters dialog

By default, there is one model per worksheet, and the variable, constraint and objective cell selections for this model are saved 'behind the scenes.'

The model displayed in the Solver Parameters dialog is the one for the worksheet that is active when you select Tools Premium Solver. But you can create additional named models if you use PSI functions for optimization.

< Back to Premium Solver Platform Product Overview