SOLVED
Home

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
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
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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies