Excel Troubleshooting Guide

By: Bryan Povlinski

Install Solver

The first step you need to take with any of our tools is to install the Solver plugin in Excel. It’s free and takes about 15 seconds to get set up. Here’s how to do it in each version of Excel.

Windows Excel

https://support.office.com/en-za/article/Load-the-Solver-Add-in-0e6760e3-dab5-4fd4-bebb-15ee311a4316

Excel for Mac 2011

http://www.solver.com/welcome-mac-users-solver-now-included-excel-2011

  1. Click on Tools > Hover over Macros and choose VBA Editor
  2. Then click on Tools again and choose References
  3. When the reference box opens up you want to make sure the box next to “Missing Solver.XLAM” is unchecked and “Solver” is checked.

Excel for Mac 2016

https://support.office.com/en-au/article/Load-the-Solver-Add-in-in-Excel-2016-for-Mac-d7a34027-56bf-4569-9b42-45a624c3e774

Additional Instructions if you don’t see the Solver plugin as an option

http://www.solver.com/excel-solver-how-load-or-start-solver

Additional Requirements for Specific Versions of Excel

Excel 2016 for Windows

If you’re on Excel 2016 for Windows there is somewhat of a work-around that you need to do first when you open Excel because it seems like Microsoft hasn’t correctly configured their file location for the Solver plugin when you install Excel. I’m working on a more permanent fix now. Here’s what you’ll need to do:

  1. With Excel closed, open the attached file.
  2. Click on Data > Solver and then Solve
  3. Now open the Projection Tool file (without closing Excel)
  4. Exit out of the Reset Solver file – you can save it on your machine if this problem happens again
  5. Click the Find Optimal Lineup button as you normally would.

Reset Solver File

Excel 2007

You may have to shut down Excel compeltely and re-open the file to resolve errors in Excel 2007 recognizing the Solver plugin

Excel for Mac 2011

The Multi-Lineup Optimizer will not work on Excel for Mac 2011. This is due to a lack of VBA support to run the Solver plugin multiple times at once.

If you’re running Excel for Mac you shouldn’t have to worry about installing, but you will need to complete this extra step:

  1. Click on Tools > Hover over Macros and choose VBA Editor
  2. Then click on Tools again and choose References
  3. When the reference box opens up you want to make sure the box next to “Missing Solver.XLAM” is unchecked and “Solver” is checked.

If you don’t see Solver in that list to check please follow the instructions on this page: http://www.solver.com/welcome-mac-users-solver-now-included-excel-2011

Excel for Mac 2016

There are several additional steps that need to be performed manually if you’re using Excel for Mac 2016. These steps should only take an extra 30 seconds and they’re all listed explicitly on the tab where they’re relevant.

Troubleshooting Error Messages

Compile Error – Can’t find Project or Library

This means the Solver plugin is not installed or not recognized in Excel. Follow the instructions above to install Solver. If you already have it installed, shut down Excel completely and re-open the file.

HResult Error on Excel 2016 for Windows

See the instructions above specific to Excel 2016

Getting #N/A’s in the Optimal Lineup

The most likely cause is that the Solver plugin is not installed or connected properly. You’ll want to follow the instructions for installing Solver above.

This can also happen when changes are made to the Optimal Lineup tab. If you change any of the values beyond just hitting the Find Optimal Lineup button you’re likely going to run into problems. You’ll want to log back in to the site and download a fresh file.

Invalid lineup (over the cap, not the right positions)

You’ll want to make sure the Fantasy Site on the Projections tab is set to the site you’re trying to get a lineup for.

If you’ve made any changes to the Optimal Lineup tab beyond hitting the Find Optimal Lineup button you’ll want to download a fresh sheet.

This can also be related to not having the Solver plugin fully installed and enabled. Follow the instructions above for installing the Solver plugin

Sheet won’t refresh (only see players from previous day/week)

If you’re using the NFL tool you’ll want to make sure you download a new file. The new sheets are uploaded to the site every Wednesday morning Login at www.spreadsheet-sports.com/nfl-update to get the new sheet.

The NBA sheet should be refreshable during the week. However, a new sheet gets uploaded every Monday morning and you’ll want to make sure you have the latest. Login at www.spreadsheet-sports.com/nba-update to get the new sheet.

The refresh process connects to multiple websites to pull in the data that it needs to provide up to the minute information. If you attempt to refresh when you’re not connected to the internet you won’t get any new data. Also, if you’re on a network (maybe while you’re at work) that blocks access to certain sites like Google Docs, sports websites like Rotoworld, or others then you won’t get the latest data. There is no work-around for this. You need to be on a network that allows access to all web pages.

The refresh process is setup to continue through each step even if one of the steps fails. Because of this you may not see an error when you click on Refresh. You’ll just see that the data is not refreshed after you think it’s finished.

To test whether the refresh is working you can “force” a refresh on any individual tab. Typically, I would start with the tab titled Fanduel (in MLB it’s titled “FanDuel Contest Data”) or the tab titled DraftKings (in MLB “DK Data”). Right click on the top left cell of that tab and choose Refresh.

If you get an error there are 3 potential reasons:

  1. Your browser is set to not save encrypted pages to disk. To resolve:

In Internet Explorer (must be I.E. not another browser because Excel uses I.E> to connect to data on the web) click on the settings gear on the right and choose Internet Options >Advanced, then scroll down until you see the tick box for ‘Do not save encrypted pages to disk’. Just un-check that, then close I.E.

Cannot access URL during refresh

  1. Excel is stripping out part of the URL on your machine. To resolve:

See the top right of the Fanduel or DraftKings tab. Find the Connection URL. Copy that URL. Then right click on the top left cell and choose Edit Query. Make sure the connection URL is being used. If you see an error message for “Continue running scripts on this page?” Just click Yes. Make sure you click the yellow box in the top left to pull in data from the entire page. Then click Import.

  1. You’re running an older version of Internet Explorer that cannot access Google Docs (where the salary data is stored). To resolve: Update Internet Explorer to the latest version.
  1. You don’t have Data Connections enabled in Excel. To resolve:

File > Options > Trust Center > Trust Center Settings > External Content. Don’t use the setting for Disable without Notification. All other settings are fine.

Macro not Found or Unable to Open Projection Tool in Excel

Instead of opening the file directly from the browser download, save the file to a location on your computer (desktop, etc), open Excel on its own, and then do a File > Open from Excel to open the Projection Tool file.

If Macros are not found or enabled, you should get a yellow bar that appears above the formula bar that asks you if you want to enable. Click yes. To manually figure your macro settings click File > Options > Trust Center > Trust Center Settings > Macro Settings. Don’t use the setting for Disable without Notification. All other settings are fine.

Run-time error 9 – subscript out of range

The “subscript out of range error”, is caused by one of the following issues:

  1. a) Not saved on your computer with the exact same name as it’s uploaded on the site (your browser could have added a (1) at the end when you downloaded it and that needs to be removed when you save it
  1. b) The Solver file is not open in the same instance of Excel (although don’t worry about that in Excel 2013 because all files open in their own stand-alone window and it will work fine there)
  1. c) Editing is not enabled on the Solver file. You should see a yellow notification bar below the formula bar if it’s not yet enabled.

The most likely cause is the file name so make sure you have those exactly as they were when you downloaded from the site.

Maximum Time Reached during Solver run

Allow Excel to continue. Make sure you don’t have any other files open in Excel and if you’re running other programs that would take up a lot of memory you’ll want to close those as well.

General Troubleshooting