Skip to main content

Qlik NPrinting

Discussion Board for collaboration on Qlik NPrinting.

Announcements
Defect acknowledgement with Nprinting Engine May 2022 SR2, please READ HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
lauranvh
Partner - Creator
Partner - Creator

NPrinting Excel Template: Conditional Formatting per row

Hello everybody,

My question is about conditional formatting. I'm creating a table that is directly from Qlik Sense with the property: Keep Source Formats off.
I'm trying to get the last row of this table to be in a different number format.
Also I want to get 2 rows bold.
Both of these should be achieved with conditional formatting.

The start of the table is like this
Conditional Formatting per row 1.png
The last column in the picture has an if statement that fills the rows with 1 and 2. which rows have to be bold and which row has to be different number format.

Then I make a Conditional formatting rule with a formula while having the Januari to Juni columns and row 28 and 29 selected
So the formula is r28to29. When previewing this it uses the complete table (with 19 rows). So R28 till R46. But I don't think that would is what I need. If it would work all of the rows and selected columns are going to be changed to the other number format. And only the row with the 1 should be changed.
Conditional Formatting per row 2.png

This is what it shows when I run the preview. I don't think the formula works because the output from that is never going to be 1. And the area with all the rows is to large now with all of the rows that are in there.
Conditional Formatting per row 3.png

For the number 2 (bold) I should create a different conditional formatting rule but let's first tackle this one.

The whole table looks like this. Just for reference. The row that starts with 19 should be in a different number format

Conditional Formatting per row.png

I hope this is clearly explained and people can help me. 
I'm not completely sure if this could work at all but maybe there are people that have done this already.

Labels (2)
1 Solution

Accepted Solutions
Lech_Miszkiewicz

Hi,

You are making one small mistake when defining condition. You are providing range instead of a cell reference in the formula which checks the condition and that is why it does not work for you.

You are doing:

  • Formule: $R$28:$R$29 = 1
  • Van toepassing op: $F$28:$K$29

You should be doing: 

  • Formule: $R28 = 1
  • Van toepassing op: $F$28:$K$29

Please note couple of things:

1 - I only use $ to lock reference  on the column( $R28)and not on the row - that allows condition to be populated on all rows below.

Please see screenshot of my small sample:

1.jpg2.jpg

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.

View solution in original post

3 Replies
Lech_Miszkiewicz

Hi,

You are making one small mistake when defining condition. You are providing range instead of a cell reference in the formula which checks the condition and that is why it does not work for you.

You are doing:

  • Formule: $R$28:$R$29 = 1
  • Van toepassing op: $F$28:$K$29

You should be doing: 

  • Formule: $R28 = 1
  • Van toepassing op: $F$28:$K$29

Please note couple of things:

1 - I only use $ to lock reference  on the column( $R28)and not on the row - that allows condition to be populated on all rows below.

Please see screenshot of my small sample:

1.jpg2.jpg

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
Lech_Miszkiewicz

One more comment:

please check in the output if your 1 & 2 value in last column is returned in excel as text (left alligned with little green triangle in left top corner of excel cell, or numerical - plain cell with value right alligned.

It is important as your formula might be:

* for text column

  • Formule: $R28 = "1"
  • Van toepassing op: $F$28:$K$29

* for numerical column

  • Formule: $R28 = 1
  • Van toepassing op: $F$28:$K$29
cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
lauranvh
Partner - Creator
Partner - Creator
Author

Thank you very much for this reply.
It worked wonders and I understand what I did wrong. 

Your post is now accepted as the solution. Hopefully this helps people in the future!