topic Re: NPrinting Excel Template: Conditional Formatting per row in Qlik NPrinting

NPrinting Excel Template: Conditional Formatting per row

lauranvh — Tue, 06 Jul 2021 13:19:54 GMT

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

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.

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.

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

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.

Re: NPrinting Excel Template: Conditional Formatting per row

Lech_Miszkiewicz — Tue, 06 Jul 2021 23:54:37 GMT

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:

Re: NPrinting Excel Template: Conditional Formatting per row

Lech_Miszkiewicz — Wed, 07 Jul 2021 00:06:58 GMT

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

Re: NPrinting Excel Template: Conditional Formatting per row

lauranvh — Wed, 07 Jul 2021 09:25:27 GMT

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!