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.
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 addin 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 the HowNow Smart Workpapers Formulas.
In Excel, go to File / Options, then select the Addins button the left hand side. In a correctly working installation, you should see the following:
To diagnose problems, from this screen, next to Manage, ensure 'Excel Add-ins' is selected and press 'Go...'
The following possibilities have been observed with corresponding resolutions:
1) HowNow Smart Workpapers Formulas does not exist at all
The issues arise because the XLL Addin component of the HowNow Smart Workpapers, which contains a number of custom formulas, is not installed or registered correctly. Refer to the Installation article to confirm that your installation is working correctly.
You can check the registry at the below to confirm their 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.HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options
To quickly re-register the add-in without doing a full re-install, versions 22.214.171.124 onwards have a 'RegisterAddins.bat' file in the installation directory which you can double-click on to register the HowNow Smart Workpaper Formulas addin.
2) HowNow Smart Workpapers Formulas exists but is not ticked
The add-in has been disabled. Tick this to re-enable the addin. 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 administrator before enabling the add-in.
3) HowNow Smart Workpapers Formulas exist and is ticked
The add-in would appear to be loaded correctly. You can try the following:
- Try pressing 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!, we have only one known client with this issue, and we have currently escalated this issue to Add-in Express.
4) 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 addin as well as the ‘multi-user’ addin incorrectly. Our installation instructions explicitly 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.