

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dynamic referencing Excel report
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
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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..

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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")


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Lech_Miszkiewicz ,
I have built a small example for you, which reflects the problem quite well.
Hope it helps you.
Thank you.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
yes it was, but easier than I thought, but you have to get the idea first 🙂
Thanks for your help.
Greetings
