Share via

conditional formatting structured references

Anonymous
2013-09-05T03:05:35+00:00

Hi, can any body confirm this asymmetry?

I can't use structured references in conditional formatting formulas.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-09-05T11:05:49+00:00

    Hi,

    Any structured reference throws an error in CF but there's a workaround.

    This very simple formula fails if used in CF but works fine on the worksheet:-

    =SUM(Table1[Sales])>100

    Include the use of the INDIRECT function and it works in CF

    =SUM(INDIRECT("Table1[Sales]"))>100

    I have never found it documented anywhere so cannot confirm that all structured ref's would fail but have never found one that works<g>

    Was this answer helpful?

    9 people found this answer helpful.
    0 comments
  2. Anonymous
    2013-09-05T11:45:30+00:00

    Thanks for that Mike.

    Its nice to get confirmation.

    Yep, I could use indirect but its not nice.  If the name of the table is evolving or some one edits the headings then its a bit of a pain.  My work around was to wrap it in a named range.

    It seems like weird stuff is going on with Chart series and structured refs as well, even when wrapped in a named range, but that's another story...

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2014-10-29T23:59:04+00:00

    I came across this thread when looking for a solution to a problem I had in the same area. I had a file which worked in Excel 2007 and 2010 but crashed Excel 2013. Eventually I discovered that the crash was related to using a structured formula via a data name in a conditional format.

    Was this answer helpful?

    0 comments