Conditionally format Table columns based on the Column Header value (using structured references)

[diversification]

Hi there, I've got a Table (let's call it Table1) with a number of 'spacer' columns in it. Those spacer columns each have the word "spacer" somewhere in their column headers. For example, a header might be "Spacer Column 3" or "SPACER66" and so on. I want to conditionally format all the spacer columns so that they're blacked out.

I know structured references can be used with conditional formatting by way of the INDIRECT function, however I'm having a difficult time figuring out how to even reference the column header of a specific cell using a structured reference. If I could do that, I could then search the header for "Spacer", and combine that with the indirect function to format entire columns whose headers contain the word "spacer."

I was really hoping that

=Table1[@Headers]

would spit out the header text, but it doesn't. Does anyone know of a way to do this? For the record, if that function did work, I'd then use something like

=SEARCH("SPACER",INDIRECT("Table1[@Headers]"))>0

to handle the conditional formatting I mentioned.

 

[Fluff]

Why not just use

=ISNUMBER(SEARCH("spacer",A$1))

where A1 is the first 1st header in your table.

 

[diversification]

Why not just use

=ISNUMBER(SEARCH("spacer",A$1))

where A1 is the first 1st header in your table.

Unfortunately, the Row location of the headers is variable. I should've mentioned that's one of the reasons why I'm interested in using the structured reference method.

 

[Fluff]

Once you setup the rule, then it will follow the table if it's moved.

 

[diversification]

Once you setup the rule, then it will follow the table if it's moved.

Good point - that's probably good enough. I do hope that in a future release they provide a way to do this using a structured reference to make it even more bullet proof. That and allowing structured references naturally in conditional formatting rules. Wishful thinking maybe, but a guy can hope! Marked as answered. Thank you.

 

[Fluff]

Glad to help & thanks for the feedback.