topic Re: Excel formula referencing another sheet in Qlik NPrinting

Excel formula referencing another sheet

guilhermetiscos — Sat, 16 Nov 2024 04:44:23 GMT

Hello,

I have an Excel report with 2 sheets: Resumo and DTL. At the Resumo sheet I have an excel SUMIF formula that references the DTL sheet. Running the report results in the following error:

Instead of pointing to the same files DTL sheet, it references to the temp file used in Nprinting Designer. Is it an expected behavior? Can it be solved? Is it a bug?

Re: Excel formula referencing another sheet

Lech_Miszkiewicz — Mon, 21 Jan 2019 02:47:19 GMT

Hi,

this is really Excel question - not NPrinitng question.

I will attempt to answer it and i will say that it depends how you set your references.

What you are doing now is setting reference to a excel document together with the path where document is stored. From my experience i was always able to avoid hardcoding the path and was able to have a reference to the same document.

Could you please provide us with the screenshot of your formula in NPrinting template editor - the one you have already provided is only result, and we need also to see how you have set it up.

cheers

Lech

Re: Excel formula referencing another sheet

guilhermetiscos — Mon, 21 Jan 2019 14:01:24 GMT

Hi, Lech,

Thank you for your help, and sorry, my mistake for not putting all the evidences:

Here is the print SS of the NPrinting Designer. As you can see, the formula is not hardcoded to the path of the template file:

And here is the generated report after running the Preview:

I have other reports that this 'formula reference' works as expected, the only difference I see is the use of a Table as a Level in this one. Could this be the source of the error? I used this functionality because I need the formulas from F5:M5 to be replicated to the new lines created from the origin table (a simple Straight table).

Re: Excel formula referencing another sheet

Steven_Haught — Wed, 30 Dec 2020 15:57:26 GMT

@Lech_Miszkiewicz @guilhermetiscos

Was there ever a solution to this?

Re: Excel formula referencing another sheet

guilhermetiscos — Wed, 30 Dec 2020 16:33:18 GMT

Hello Steven,

The solution I found to this problem is to use Named Ranges when the formula references another sheet's cell(s).

If you don't know what Named Ranges are, here is some guidance: https://trumpexcel.com/named-ranges-in-excel/

Hope it helps.

Re: Excel formula referencing another sheet

Steven_Haught — Wed, 30 Dec 2020 18:39:06 GMT

@guilhermetiscos

That worked perfect, thank you for the quick response and answer!

Re: Excel formula referencing another sheet

Lech_Miszkiewicz — Sat, 02 Jan 2021 03:43:27 GMT

yes-named tables can be used to populate formulas or you have to built your excel table using NPrinting level method.