Why do my TSLookup and TSFind functions return zero after optimizing my workbook?

Symptom

After upgrading to Office Connector version 2.01.0041 the following message is displayed when opening a workbook that was saved using a prior version of Office Connector:

• This workbook was saved using a prior version of Office Connector. This workbook needs to be optimized to run faster under the new version. If you do not optimize this workbook, it may take longer to refresh than it did before.
  
  Do you want Office Connector to optimize your workbook now?
  
  <Yes> <No>

Upon clicking Yes, another message is displayed stating “This workbook has been successfully optimized. You must save the workbook in order to keep the optimizations”. Following this, Office Connector functions within the workbook return a value of zero instead of the expected values.

Resolution

Option 1 - Disable the performance optimization features for the workbook

This option will cause will allow the workbook to function normally but without the benefit of the performance enhancements. In some cases this action may result in somewhat slower performance as compared to previous versions of Office Connector.

Office 2010 and above

1. Open the desired workbook in Excel
2. Click File
3. Click Properties
4. Click Advanced Properties
5. Click Custom
6. In the Name box, type: UseFilters
7. In the Value box, type: 1
8. Click Add
9. Click OK
10. Save the workbook
11. Close and re-open the workbook and confirm that no prompt appears asking if you want to optimize the workbook and then confirm that the formula values return the expected results.

Option 2 - Complete the optimization by unloading and then reloading the Office Connector add-in

1. Close the workbook without saving
2. Close and restart Excel
3. Open the workbook in question
4. When prompted to optimize your workbook, click No
5. Unload and reload the Office Connector files using these instructions:
   How do I unload and load the Office Connector add-in?
6. Click Refresh on the Office Connector toolbar
7. When prompted to optimize your workbook, click Yes
8. If formula values now return the expected values, save the workbook
9. If the problem persists close the workbook without saving and use Option 1 listed above to disable the performance optimization feature
10. Close and reopen the workbook to verify that you are not prompted to optimize the workbook and that the formulas show the expected values