Nprinting - Excel conditional formatting works in Template Editor, not in report.

[SHurleySteward]

I have a NPrinting Excel document in which depending on the field I'm cycling displays a second set of columns or not.  It also include Excel sparklines.  I'm having problems getting oulines around the sparkline cells.  For visual ease I've changed the conditional format to change the background yellow.

If Table6Header is blank, everything works fine.  The problem is when it's supposed to display.

If the <Table6Header> has a value, this is how it's showing in the template editor - exactly what I'd expect

And if I clear out the heading I get (which when run for real makes columns Y-AB look to totally blank):

But when I run the report, this is what I get:

I fully expect columns Y, Z and AA to get a white background (or whatever color), because I have the table marked to Keep Source Formats.  However, column AB is not pulling from a QV table - it's a sparkline created in the Excel document.  I expected that column AB would have been all in yellow.  So why isn't it? 

I've tried a variety of Keep Source Formats in the document.  Removed all Keep Source Formats on the Levels.  I'm either doing something wrong or I've missed some documentation that tells me I can't do what I'm attempting. 

Any ideas?  Thanks.

[Lech_Miszkiewicz]

so your condition checks for value only in cell "P1" as you are freezing it by using $ sign in front of column name and row number $P$1. This is how excel works, right?

So if your intention is to have conditional format based on corresponding value from the same row in column P you would need to use $P1. 

I mean - this is just excel behaviour, so it would be good to see result and actual values in P column as well as applied conditional format on resulting cell - that way you can tell how it got applied. This is however hard to do with the screenshots provided.

[Lech_Miszkiewicz · Accepted solution]

EDIT !!!

Hi @SHurleySteward  - I missed your point little bit with my answer above - sorry, but I would still try to debug this by doing following:

• generate report as you currently do
• unhide cell P1 - or at least make it 0.1 width!!!
• go to sparkline cell and check what is set in this cell conditional format
  • is it possible that conditional formatting is removed?
  • is it possible that your reference cell is removed and you see #REF! error?
  • maybe you are referencing merged cell which may/may not cause issues?

cheers

[SHurleySteward]

Changed $P$1 to $P1.  Unhid column P.  Same results.  BUT.... you're right about the #REF error.    I have to put conditions on each row instead of just running off the header, but that has done the trick!  I now see colors in the final product.

Thanks so much!