Dynamic referencing Excel report

[Danijel]

Hello all,

I am currently building an NPrinting report in Excel. My current problem is to access different totals.

In my example, I am trying to access a value (Total A) from the top section and offset it in the bottom section, which works. However, the whole thing doesn't work on Total B and C anymore, since NPrinting doesn't access the Totals, but only moves the number of rows down.

Is there any way to reference this dynamically? Meaning that I always access the respective total row in the formula?

Designer

Result

[Lech_Miszkiewicz]

Hi - sorry I dont understand your question well so I will just try to guess it...

If your goal is to be able to reference the same formula for total in bottom table and then somehow dynamically pick for that formula values from row in the top table i would suggest to use some sort of reference functions which would be able to reference Total A or Total B... you may need to consider VLOOKUP to search for correct total line and not reference in your formulas single cell but range. That way from range only VLOOK'ed-UP value could be used...or you may use SUMIF() or similar functions.. 

[Lech_Miszkiewicz]

so.. based on your template screenshot I would do something like below (assuming that Totals in column B have the same labels between top and bottom section): 

IF((E18+F18+L18)>0;SUMIF(B6:B10,B18,G6:G10)/((E18+F18+L18)/C18);"N/A")

[Danijel]

Hi @le ,

thank you for your answer. Sorry, unfortunately I do not really know how to describe the problem.

However, your idea and interpretation with the sumif was not bad.

And yes the areas A B and C are identical above and below.

I tried to implement that, however NPrinting shifts the areas (see screenshot) awkwardly. Here I worry that the range times values will not catch or use the values from below, which it should not do.

[Lech_Miszkiewicz]

I understand that NPrinitng may shift records - this is dependent on how you wrap your tags and levels together. You need to remember that rows where level tags are placed may cause that shift as they will disappear during generation process. I suggest you practice this first... Place where you have levels, places where you have <deleterow> tags... all that needs to be taken into account for your range to remain in place. I understand that this app has some real data in it hence you cannot use it, but if you build a dummy with Qlik Sense Ctrl +0+0 script and some NPrinitng report of that then we can have working sample which I would be able to guide you through. Without the actual example it will be hard to explain it

[Danijel]

Hi @Lech_Miszkiewicz ,

I have built a small example for you, which reflects the problem quite well.

Hope it helps you.

Thank you.

[Lech_Miszkiewicz · Accepted solution]

That was simpler than i thought.... I did not even think that you missed $ sign in your excel formulas which is needed to keep the range and not allow to drag it down with the row references....

If you were unaware of it here is simple explanation: https://support.microsoft.com/en-us/office/switch-between-relative-absolute-and-mixed-references-dfe...

you can see on template that i am creating sum (based on SumIf() concept) for cell marked in yellow and on another picture is a result. I also attached the nprinting template export.

cheers

[Danijel]

Hi @Lech_Miszkiewicz 

yes it was, but easier than I thought, but you have to get the idea first 🙂

Thanks for your help.

Greetings