CODEWITHSUNDEEP
excelexcel-formulaconditional-formatting
Elie G.
Elie G.

Reputation: 1723

Excel - Conditionnal formatting with formula not working but the formula itself works

I'm trying to format a column of file paths depending on their extension (e.g. .css).

The said column of file paths looks like the following (3rd column)
column of file paths

In another sheet, I have the following table named FileExtensionsTbl in which every column contains the extension for a conditional format. For example, all the extensions in column CSS would be used to match a specific style like all files ending with .css would match the CSS conditional format.
File extensions table

I came up with a formula that works when used in the same sheet where the file paths are.

Here is the formula:
=NOT(ISNA(MATCH(LOWER(RIGHT(RC3; LEN(RC3)-FIND(".";RC3)+1)); FileExtensionsTbl[CSS]; 0)))

Here is the result (4th column):
Formula result

The problem is that I can't figure out a way to make it work as a formula for conditional formatting.

Here is the error I get when I try to use it in conditional formatting:
Conditionnal format error

Note: I'm using the R1C1 reference notation (relative)

Upvotes: 0

Views: 177

Answers (1)

Steffen Bobek
Steffen Bobek

Reputation: 642

It seems you can't use structured references in conditional formatting formulas. Two suggestions:

1) Replace FileExtensionsTbl[CSS] by its column reference. Assumed CSS types are on the sheet File Extensions in column A your formula would be:

=NOT(ISNA(MATCH(LOWER(RIGHT(RC3; LEN(RC3)-FIND(".";RC3)+1)); 'File Extensions'!A:A; 0)))

2) Or use this trick and enclose the structured reference with INDIRECT:

=NOT(ISNA(MATCH(LOWER(RIGHT(RC3; LEN(RC3)-FIND(".";RC3)+1)); INDIRECT("FileExtensionsTbl[CSS]"); 0)))

Upvotes: 1

Related Questions