SOLVED

Empty query table breaks conditional formatting comparing adjacent rows. Any work around?

%3CLINGO-SUB%20id%3D%22lingo-sub-134542%22%20slang%3D%22en-US%22%3EEmpty%20query%20table%20breaks%20conditional%20formatting%20comparing%20adjacent%20rows.%20Any%20work%20around%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-134542%22%20slang%3D%22en-US%22%3E%3CP%3EUsing%20two%20different%20versions%20of%2032-bit%20Excel%202016%20(16.0.4549.1000%20at%20home%20and%2016.0.8201.2207%20at%20work)%20on%20Win%2010%2C%3C%2FP%3E%0A%3CP%3EI%20have%20a%20table%20with%20headers%20starting%20at%20A1%20that%20retrieves%20data%20from%20an%20sql%20database.%20%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20select%20the%20table%20body%20rows%2C%20say%20A2%3AC5%20and%20apply%20conditional%20formatting%20to%20color%20cells%20with%20%3D(A1%3DA2)%2C%20so%20a%20cell%20is%20highlighted%20whenever%20it%20matches%20the%20cell%20above.%3C%2FP%3E%0A%3CP%3EAll%20works%20great%20as%20I%20refresh%20the%20query%2C%20unless%20the%20query%20result%20is%20ever%20empty%20--%20then%20the%20conditional%20formatting%20breaks%20in%20one%20of%20two%20ways%3A%26nbsp%3B%20the%20A1%20reference%20become%20%23REF!%20or%20becomes%26nbsp%3B%3CSPAN%3EA1048574%3C%2FSPAN%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20do%20I%20prevent%20this%2C%20or%20fix%20it%20without%20manually%20repairing%20the%20conditional%20formatting%20each%20time%3F%3C%2FP%3E%0A%3CP%3E(VBA%20workarounds%20would%20be%20fine.)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20reproduce%2C%20you%20can%20make%20a%20query%20to%20an%20Excel%20file%3A%3C%2FP%3E%0A%3CP%3EMake%20a%20small%20table%20in%20excel%2C%20save%20as%20tmp.xlsx.%20Then%20Data%26gt%3BGet%20External%26gt%3BFrom%20Other%26gt%3BMicrosoft%20Query%2C%20select%20Excel%20Files.%26nbsp%3B%20In%20the%20picker%2C%20check%20Read%20only%20and%20point%20to%20tmp.xlsx.%20%26nbsp%3BOptions%26gt%3Bcheck%20systems%20tables%20so%20you%20can%20select%20Sheet1%24%20as%20the%20input%20table%2C%20click%20*%20to%20get%20all%20data%2C%20and%20File%26gt%3BReturn%20to%20Excel%2C%20and%20choose%20to%20put%20the%20table%20at%20A1.%20%26nbsp%3B%20You%20now%20have%20a%20query%20table%20that%20retrieves%20its%20data%20from%20tmp.xlsx%3C%2FP%3E%0A%3CP%3ESelect%20the%20table%20body%20(not%20the%20headers)%20and%20add%20conditional%20formatting%20rule%20to%20fill%20cells%20with%20%3D(A1%3DA2)%2C%20i.e.%2C%20if%20it%20matches%20the%20cell%20above.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDelete%20the%20table%20rows%2C%20then%20right%20click%20in%20the%20table%20and%20select%20Refresh.%3C%2FP%3E%0A%3CP%3EThe%20conditional%20format%20rule%20now%20reads%20%3D(%3CSPAN%3EA1048574%3DA2%3C%2FSPAN%3E)%3C%2FP%3E%0A%3CP%3EFix%20it%2C%20then%20delete%20the%20data%20lines%20in%20tmp.xlsx%2C%20leaving%20the%20headers%2C%20and%20save.%26nbsp%3B%20Refresh%20the%20query%20table%20and%20the%20conditional%20formatting%20rule%20reads%26nbsp%3B%3CSPAN%3E%3D(%23REF!%3C%2FSPAN%3E%3CSPAN%3E%3DA2%3C%2FSPAN%3E%3CSPAN%3E).%20Add%20data%20back%20and%20refresh%20again%2C%20and%20it%20remains%20broken.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-134542%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-137401%22%20slang%3D%22en-US%22%3ERe%3A%20Empty%20query%20table%20breaks%20conditional%20formatting%20comparing%20adjacent%20rows.%20Any%20work%20around%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-137401%22%20slang%3D%22en-US%22%3EExcellent.%20Thank%20you!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-137387%22%20slang%3D%22en-US%22%3ERe%3A%20Empty%20query%20table%20breaks%20conditional%20formatting%20comparing%20adjacent%20rows.%20Any%20work%20around%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-137387%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jack%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20resulting%20table%20use%20structured%20references%20for%20the%20conditional%20formatting.%20It%20doesn't%20work%20with%20them%20directly%2C%20the%20workaround%20is%20to%20use%20INDIRECT.%20If%20the%20name%20of%20your%20resulting%20table%20is%20Tbl%20and%20table%20column%20name%20is%20%22a%22%20when%20like%3C%2FP%3E%0A%3CPRE%3E%3DINDIRECT(%22Tbl%5B%40a%5D%22)%3DOFFSET(INDIRECT(%22Tbl%5B%40a%5D%22)%2C1%2C0)%3C%2FPRE%3E%0A%3CP%3Einstead%20of%20%3DA1%3DA2%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-719633%22%20slang%3D%22en-US%22%3ERe%3A%20Empty%20query%20table%20breaks%20conditional%20formatting%20comparing%20adjacent%20rows.%20Any%20work%20around%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-719633%22%20slang%3D%22en-US%22%3EBrilliant!%20thank%20you!%3C%2FLINGO-BODY%3E
Highlighted
Deleted
Not applicable

Using two different versions of 32-bit Excel 2016 (16.0.4549.1000 at home and 16.0.8201.2207 at work) on Win 10,

I have a table with headers starting at A1 that retrieves data from an sql database.  

I select the table body rows, say A2:C5 and apply conditional formatting to color cells with =(A1=A2), so a cell is highlighted whenever it matches the cell above.

All works great as I refresh the query, unless the query result is ever empty -- then the conditional formatting breaks in one of two ways:  the A1 reference become #REF! or becomes A1048574.

 

How do I prevent this, or fix it without manually repairing the conditional formatting each time?

(VBA workarounds would be fine.)

 

To reproduce, you can make a query to an Excel file:

Make a small table in excel, save as tmp.xlsx. Then Data>Get External>From Other>Microsoft Query, select Excel Files.  In the picker, check Read only and point to tmp.xlsx.  Options>check systems tables so you can select Sheet1$ as the input table, click * to get all data, and File>Return to Excel, and choose to put the table at A1.   You now have a query table that retrieves its data from tmp.xlsx

Select the table body (not the headers) and add conditional formatting rule to fill cells with =(A1=A2), i.e., if it matches the cell above. 

Delete the table rows, then right click in the table and select Refresh.

The conditional format rule now reads =(A1048574=A2)

Fix it, then delete the data lines in tmp.xlsx, leaving the headers, and save.  Refresh the query table and the conditional formatting rule reads =(#REF!=A2). Add data back and refresh again, and it remains broken.

 

 

3 Replies
Highlighted
Best Response
Solution

Hi Jack,

 

With resulting table use structured references for the conditional formatting. It doesn't work with them directly, the workaround is to use INDIRECT. If the name of your resulting table is Tbl and table column name is "a" when like

=INDIRECT("Tbl[@a]")=OFFSET(INDIRECT("Tbl[@a]"),1,0)

instead of =A1=A2

Highlighted
Highlighted