Excel: Why is this conditional formatting formula not working?

1
votes

I have an Excel spreadsheet (don't ask), with a 4x6 table with headers Day 1, Day 2, Day 3, and Total. However, a formula is changing the location of the Total according to the date, so at any point Day 3 could be removed and instead replaced with Total if the date detects that we are only up to Day 2, so we needn't display headers for Day 3 yet.

I wanted to use conditional formatting to style everything under Total a certain way, so I wrote this formula for the option Use a formula to determine which cells to format:

=IF(INDIRECT(CONCATENATE(CHAR(COLUMN()+64), 1)) = "Total", TRUE, FALSE)

This should be formatting any cell where the cell in its column's first row is equal to "Total". However, it simply never styles anything!

To troubleshoot, I attempted selecting just one column (i.e C) and applying a modified form of the rule just to that:

=IF(INDIRECT(CONCATENATE("C", 1)) = "Total", TRUE, FALSE)

... It worked! Then, I tried simply putting the first formula into a cell, and it was displaying TRUE and FALSE correctly. Everything seems to work, but for some reason that I could not figure out the conditional formatting would never apply.

Can someone please explain why?

PS in the question I wrote a simplified example, in the real thing I have 31 days instead of three, so it would be a major hassle to go through each column individually

2
excelexcel-formulaconditional-formatting
indirect shouldn't be used in named ranges or conditional formatting rules. try =column(a1)=match("total", row(1:1), 0) as a cfr formula with a refersto of A:C. - user4039065
Does ...CONCATENATE(C,1)... work? You don' have to do ...(C:C,1)...? Also, check the Applies To range of your conditional format to make sure the range matches the formula/expected formatting. - BruceWayne
BruceWayne - Sorry, that was a mistake; it was meant to say CONCATENATE("C", 1), but I missed out the speech marks. - Geza Kerecsenyi
@Jeeped - sorry, but that didn't seem to work for me. It is still having no result. - Geza Kerecsenyi

2 Answers

1
votes

INDIRECT(CONCATENATE(CHAR(COLUMN()+64), 1)) is probably the worst possible way I can think of to reference the cell in the first row and same column.

Instead of this:

INDIRECT(CONCATENATE(CHAR(COLUMN()+64), 1)) = "Total"

Try this (for example, for a conditional formatting rule in the A column, and apply the rule where ever else you need it):

A$1 = "Total"

These two formulas have the same effect.

0
votes

OK... I solved this myself. Turns out that my formula was fully correct, but for some strange reason it didn't like the IF statement; I replaced it with:

INDIRECT(CONCATENATE(CHAR(COLUMN()+64), 1)) = "Total"

and it works fine!

However, I would still appreciate it if someone could explain why this happened (for future reference)