How to use Solver in Excel? Getting an error message.

Hi NahumGat,     

Welcome to the community!          

From the situation you've encountered, I deeply understand the inconvenience caused.    

Solver requires all the referenced cells to be on the same sheet. Therefore, to solve this, you should keep all related cells on one sheet by moving your input variables and constraints to the same sheet as your objective cell (the one you're maximizing) OR move your objective cell to the sheet containing your variables 

Alternative workaround: 

• Create references to the cells on other sheets in the same sheet as your objective cell
• Use another cell on the same sheet that has =Sheet2!A1 in it as your constraint value.
• For example, if your variables are on Sheet2, put =Sheet2!A1 in your working sheet

In the meantime, Here are some troubleshoot methods you can try: 

Method 1: Ensure Solver is enabled 

• Before using the Solver, you need to make sure it is enabled in Excel
• Go to the "File" tab, click "Options," and then select "Add-Ins."
• In the "Manage" box at the bottom, make sure "Excel Add-ins" is selected, and click "Go."
• Check the "Solver Add-in" box, and click "OK."

Method 2: Check for Compatibility: Ensure that the version of Solver you are using is compatible with your version of Excel. If you have recently upgraded Excel, you may need to update the Solver add-in to match the new version. 

Method 3: Set up your problem correctly 

• Make sure your objective cell, variable cells, and constraints are all on the same sheet
• Click Data > Solver
• Set Objective: Select your target cell
• By Changing Variable Cells: Select your input cells
• Add constraints using the "Add" button

For reference:Excel solver adds-in issue | Microsoft Community Hub 

Method 4: Restart Excel 

If you encounter errors, try closing and reopening Excel. Sometimes, this can resolve temporary issues. 

Method 5: Check for Updates 

Ensure that your version of Excel and the Solver add-in are up to date. Check for updates from Microsoft and install them if available. 

Method 6: Repair Excel  
If a Microsoft 365 application isn't working correctly, sometimes restarting it will fix the problem. If that doesn't work, you can try repairing it. When you're done, you might need to restart your computer.   

You can following the instruction: Repair an Office application - Microsoft Support   

Note: Please understand that our initial response does not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.    

I hope this information is helpful. Please follow these steps and let me know the outcome.    

If you have any difficulties when trying these methods or the issue still persists after completing the above, feel free to reach out, and we can further investigate the problem together.    

Thanks for your patience and understanding so far.     

Looking forward to hearing from you.      

Best Regards,       

Alex - MSFT | Microsoft Community Support Specialist