How to create an Excel document having a column of invoice numbers / hyperlinks?

[waterscg]

I'm trying to use the NPrinting 19 template editor to create an Excel document having a column of invoice numbers that are also hyperlinks.

The column needs to display invoice numbers but when an individual cell is clicked in that column a corresponding unique URL needs to open a new browser tab, taking the user to a back-end, web-based, invoicing system that displays the corresponding invoice.

So each cell needs to display a value from an "Invoice Number" dimension ... but the hyperlink property of that cell needs to display the corresponding value from an "Invoice URL" dimension.

I found a few similar requests here in the community but nothing that provides a definite solution.

[Frank_S]

Chris, 

If you use NPrinting Excel Template editor and use custom template xlsm format along with the Excel HYPERLINK() function you should be able to achieve your requirement but with additional steps other than simply connection to the xlsm file.

I would encourage you to perform a search on creating an excel macro to convert text to hyperlinks to learn how to leverage that feature in MS Excel. See sample search link below:

https://www.google.com/search?q=create+an+excel+macro+to+convert+text+to+hyperlink&rlz=1C1CHBF_enCA8...

Otherwise there may be someone else here in this forum that may be able to provide more specific steps.

Kind regards...

[Lech_Miszkiewicz]

Hi @waterscg 

I have to disagree with @Frank_S  thant you have to use xlsm and even think avout macros, because you dont. Steps explained below:

1. Create an object in Qlik Sense - table which will have your Invoice number and link (link as a text)

2. look at the attached clip for demo of all the remaining steps, which are:

Option 1: use named table (clip "Table")

• bring in table to your template
• drag and drop table columns into sheet
• convert range of cells to named table in excel 
• at this stage note that my named table has one extra row and I use <deleterow> tag to remove it. It is required to populate expressions across all rows
• use hyperlink function and reference your url text as link and invoice number as friendly name
• hide columns you dont need (note that I use 0.1 with, hiding column completely will prevent this from working. 0.1 width is still enough to completely hide row an dmake it invisible)

Option 2: use excel levels (clip "Level")

• steps are almost identical as the ones with the table approach

hope this helps

[Frank_S]

Thank you @Lech_Miszkiewicz 

Your contribution here is appreciated as always.

[Lech_Miszkiewicz]

Thanks @Frank_S ,

I hope I understood initial requirement correctly, did I?

...or did I misinterpreted something from original question and we still need to use macros? 

cheers