This article will describe the process to implement Hyperlinks in Solver Cloud. Hyperlinks can be used to reference internal and external data for Reporting. This process works well in Excel but requires certain syntax to work in both Excel and the web interface.
- Note: Solver Cloud does not support hyperlinks to internal file share network location
- Hyperlinks do not work on drilldown.
While many reading this article may be familiar with the hyperlink formula, there is special syntax required to ensure that this feature will work with
- Linking across sheets
- Ensure one report will work in both Excel and the web
Make sure to read through this entire article before starting to implement hyperlinks.
This exercise assumes
- Familiarity with Solver’s Report Designer
- Intermediate Excel formula experience
In this exercise we are going to create a report off the Solver Corp Demo that creates hyperlinks between two worksheets with different entities on each sheet.
We will first get a link to work in the web.
- First we are going to do the Excel Portion with the MATCH statement. The MATCH statement will look at the other worksheet and match the values and return a Row value.
- Go to Cell A10 and enter this formula. =MATCH(D10,INDIRECT("SUS!C:C"),0)
- The MATCH looks at field in B10 and then compares it to the values in Column B using the INDIRECT Command. The 0 at the end will give an Exact Match. Run it and you will see the Rows.
- Next we add the special Hyperlink code. This combines the Excel HyperLink with the special syntax need to work in the Cloud Portal.
- HYPERLINK is the Excel Command.
- The “sjs://” is the command that is necessary in the Web Portal.
- SUS in the worksheet that we want to Hyperlink to.
- C is the Column that we want to combine with the MATCH statement to form the location to HYPERLINK to.
- Once you have this you can save the template back to the Portal and run your Hyperlink.
The above Hyperlink will only work in the Solver Portal. If you want to use Excel for Hyperlinks you need to use a standard Excel hyperlinke function.
- First you use the ADDRESS function with the MATCH. The Match works like it did above and the ADDRESS will give you the Cell Address.
- Match the D8 value with Worksheet SUS and C column
- Goes to the 3rd Column in the Worksheet
- 1 is absolute value
- The Result will look like the below
- Next add your Hyperlink in Excel
- You add the Hyperlink with the Workbook Name xlsx
- Sheet Name SUS
- ! for separate sheet name from Address
- ADDRESS formula from Step 1
- Field to reference for Hyperlink format in this case the Account Field.
- Note this will work in Excel but not in the Portal.
Combine and Display One Link to Users
You now have formulas that work in Excel and in the web, but you want to have one report that works in both. You can also combine the two concepts that work together.
- The first thing you need a true/false statement that will show if you are in Excel or the portal. This can be done with many formulas but in this exercise we will use the following statement in Cell A2.
- This will show if you have a Filename. Now go to C8 and enter this formula =ISERROR($A$2) This will show if the formula in A2 is valid or not.
- Run the report in Excel and you will see a False or there is not an Error
- Next run the report in the Portal and you will see a True Statement.
- Now we can do an IF statement with TRUE and FALSE. Combine the two statements. =IF(ISERROR($A$2),HYPERLINK("sjs://SUS!C"&MATCH(D8,INDIRECT("SUS!C:C"),0),D8),(HYPERLINK("[HyperLink 2.xlsx]SUS!"&ADDRESS(MATCH(D8,INDIRECT("SUS!C:C")),3,1,1),D8)))
- In the Formula above we made a TRUE or FALSE statement. Now we make an IF statement that you have your ERROR statement $A$2.
- IF Statement is true you have your Portal Hyperlink
- IF Statement is False you use your Excel Hyperlink.
You can look at your Excel Function to make sure it is working correctly. This completes the exercise in getting hyperlinks to work in Solver Cloud or Private Host.