Edexcel solver help

Hello,

I'm working on a scheduling model in Excel using Solver and I'm encountering issues with setting up the decision variables and constraints correctly. My workbook is divided into several sheets, and my goal is to maximize student satisfaction based on their subject preferences. Here’s an overview of my model:

Students Sheet:

Contains Student IDs and four columns for subject preferences (one per block).

Additionally, I have helper columns (for example, F, G, H, I) that sum the number of assignments for each student per block.

Classes Sheet:

Lists all class offerings with columns for ClassID, Subject, Maximum Capacity, and a Block column.

The Block column is a decision variable where Solver assigns each class to one of four blocks (integer values 1–4).

Assignments Sheet:

Features a grid where each row represents a student and each column (after StudentID) corresponds to a class (using ClassIDs from the Classes sheet).

These cells are binary decision variables (0 or 1) indicating if a student is assigned to a particular class.

Satisfaction Sheet:

Uses formulas (typically SUMPRODUCT with IF statements) to check, for each student, whether they are assigned to at least one class in each block that matches their corresponding subject preference.

It then calculates a “Total Satisfaction” value (for example, 1 if the student has a valid assignment in every block, 0 otherwise) and sums these to form the objective cell.

What I'm Trying to Achieve:

Objective: Maximize the total number of satisfied students (for example, the sum of satisfaction flags in cell I1 of the Satisfaction sheet).

Decision Variables:

The Block assignments for classes (for example, the range Classes!D2:D21).

The binary assignment grid (for example, the range Assignments!B2:U101).

Constraints to Implement:

Block Assignment Constraint: Each class’s block must be an integer between 1 and 4.

Binary Constraint: All cells in the assignments grid must be binary (0 or 1).

Capacity Constraint: For each class, the total number of students assigned (using a helper cell that sums assignments per class) must not exceed the class's maximum capacity.

Student Assignment per Block: For each student, helper cells (using SUMPRODUCT) calculate the number of assignments in each block; each must equal 1 (ensuring that every student gets one class per block).

Issue Encountered: When I try to enter the “By Changing Variable Cells” in Solver, I get errors stating that my range reference is not valid—likely because I'm trying to specify noncontiguous ranges (Classes!D2:D21 and Assignments!B2:U101) in one entry. I'm not entirely sure how to correctly input these ranges and constraints in Solver's interface without causing errors.

My Request: Could someone please provide detailed instructions or corrections on:

The exact syntax for entering noncontiguous ranges (or a workaround) in the “By Changing Variable Cells” field.

How to set up each constraint in the Solver dialog, including the correct references and options (for example, ensuring variables are marked as integer or binary).

Any help or sample screenshots of how you set up a similar model in Excel Solver would be greatly appreciated!