Symptom
When you press a hyperlink in a smart workpapers Excel starter file, you receive an error message 'Reference isn't valid', however the function will still otherwise work as expected.
In addition, inside of Excel worksheets, you will see values like #NULL! or #VALUE! or #NAME! in cells that normally work correctly.
Issue
The hyperlink is trying to find a cell reference which is calculated by one of our formulas called =CurrentCell(). These formulas are contained in an XLL Add-in called 'HowNow Smart Workpaper Formulas'.
If the formula is returning an error, it is normally because the XLL Add-in has not been correctly installed. The XLL Add-in is a per user Add-in, which means even if you have installed the multi-user version of the product, you will need to have registered this Add-in using a registration script or something similar in order for a user to have access to 'HowNow Smart Workpapers Formulas'.
Diagnose
In Excel, go to File>Options>Add-ins. In a correctly working installation, you should see the following:
To diagnose the problem, from this screen, next to Manage, ensure 'Excel Add-ins' is selected and press Go.
Resolve
HowNow Smart Workpapers Formulas does not exist at all:
The issues arise because the XLL Add-in component of the Add-in, which contains a number of custom formulae, is not installed or registered correctly. Refer to the Installation article to confirm that your installation is working correctly.
You can check the registry below to confirm there is an OPEN key pointing to a dll in our installation directory. If this is absent the Add-in has been deleted or removed (or never installed) for this user.
To quickly re-register the Add-in without doing a full re-install, versions 2.2.0.30 onwards have a 'RegisterAddins.bat' file in the installation directory which you can double-click on to register the 'HowNow Smart Workpaper Formulas' Add-in.
HowNow Smart Workpapers Formulas exists but is not ticked:
The add-in has been disabled. Tick this to re-enable the Add-in. Close Excel and open it again, checking to ensure the add-in remains ticked. If it disables itself on application restart, try opening Excel as an administrator before enabling the Add-in.
HowNow Smart Workpapers Formulas exist and is ticked:
The Add-in would appear to be loaded correctly. You can try the following:
- Press F9 to force the workbook to recalculate and see if this fixes the issue.
- Try typing in =CurrentCell() into a blank cell in the Excel workbook. If it returns a correct result then everything is working. If it returns #VALUE!, please contact support.
There is an unusual looking entry called adxloader.workpapers.next.dll or something similar:
This usually occurs when a user who has installed the normal Add-in and then the ‘multi-user’ Add-in. Our installation instructions require uninstallation of the normal Add-in prior to installing the multi-user Add-in. You need to remove this entry in Excel and reinstall. We would suggest something along the lines of:
- Install the normal add-in
- Uninstall the normal add-in
- Install the multi-user add-in
A secondary potential issue is where Windows thinks the Excel instance is running with 64-bit, so the installer loads the 64-bit version of the Add-in, while it is actually running in 32-bit (or vice-versa). In this case updating the registry key at HKCU/Software/Microsoft/Office/Excel/16.0/OPTIONS and updating the OPEN key to point to the right adxloader.dll fixes the issue.