1. Home
  2. Documentation
  3. Reporting
  4. Report Designer (Excel)
  5. Reporting Techniques
  6. Adding Excel Hyperlinks in Solver Portal
Searching...

Adding Excel Hyperlinks in Solver Portal

Contents

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. The steps below assume familiarity with Report Designer and intermediate Excel formula experience.

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 inking across sheets and ensuring one report will work in both Excel and the web

Data Load

If you would like to load hyperlinks into Solver Cloud, you will need to import data in the following format

  • https://solverglobal.com|Solverglobal Site
    • HTTP or HTTPS is required, the “www” syntax is not valid and will result in the URL being text
    • The pipe ( | ) symbol splits the URL from the friendly name and is required for the hyperlink to work in drill down.
      • The friendly name is what will appear in drill down

When reporting off of this URL, to hide the |<friendlyName>, you can use a left formula. 

=LEFT(B5,FIND("|",B5)-1)

The next section will detail reporting using the hyperlink formula and creating hyperlinks across sheets. 

Configuration

In this exercise, we are going to create a report off of the Solver Corp Demo that creates hyperlinks between two worksheets with different entities on each sheet. 

We will first get a link to work on the web.

  1. 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.  
  2. Go to Cell A10 and enter this formula. =MATCH(D10,INDIRECT(“SUS!C:C”),0)
  3. The MATCH looks at the 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.
  4. Next, we add the special Hyperlink code. This combines the Excel HyperLink with the special syntax needed to work in the Cloud Portal.
     =HYPERLINK(“sjs://SUS!C”&MATCH(D8,INDIRECT(“SUS!C:C”),0),D8)
    • 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.
  5. Once you have this you can save the template back to the Portal and run your Hyperlink.

Excel

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 hyperlinked function.

  1. 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.
    =ADDRESS(MATCH(D8,INDIRECT(“SUS!C:C”)),3,1,1)  
    • Match the D8 value with Worksheet SUS and C column
    • Goes to the 3rd Column in the Worksheet
    • 1 is an absolute value
  2. The result will look like the below
  3. Next, add your Hyperlink in Excel
    =HYPERLINK(“[HyperLink 2.xlsx]SUS!”&ADDRESS(MATCH(D8,INDIRECT(“SUS!C:C”)),3,1,1),D8)
    • 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.

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.

  1. The first thing you need is 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.
    =LEFT(CELL(“Filename”),10)  
    • 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.
  2.  Run the report in Excel and you will see a False or there is not an Error
  3. Next, run the report in the Portal and you will see a True Statement.
  4. 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)))
  5. 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.
    1. IF Statement is true you have your Portal Hyperlink
    2. 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. 

More Information

  • When using Drill-to where a URL is needed, you will have to modify your data to include a pipe “|”. Here are the requirements:
  • For integrations loading a URL, it should look like this https://solverglobal.com|solver site
    • The use of the pipe symbol is the Coding method for the comma seen in the hyperlink formula. You will want to do this so that Drilldown also includes a properly formatted hyperlink.
    • What is stored in the database must have HTTPS:// or HTTP://; just www will not work. 
    • Solver Cloud does not support hyperlinks to internal file share network location 

Updated on January 8, 2024
Was this article helpful?
Yes No

Related Articles