Auto-highlight, or Current month-based conditional formatting in Google Spreadsheet

0
votes

I'm working on a budgeting table (you can comment it, btw) and I want to make something like an auto-highlight for a current month's column.

So I have a formula 

=IF(L1=(INDEX(C1:N1;1;MONTH(TODAY())));True;False)

L1 is a cell with a month name (ranges from C1 for January to N1 for December) and the second part selects a cell of the same number as a current month from the month columns' headers (for instance, M is the 11th element, and November is the 11th month).

I'm trying to build my conditional formatting based on the formula above, so it's True if a current month matches L1 or other cell from the range and False if not.

Needless to say, it fails all the time ^_^*** In column C you see cf's behavior for this formula: - it starts highlighting from the second cell in the range, while it should not highlight at all. However, in Column N it acts properly, and in M - wrong again.

Could you please help me?

Best regards,

Alex

1
google-sheetsgs-conditional-formatting

1 Answers

1
votes

You have several conditional formatting rules in place in your spreadsheet right now, which might mess things up.

You can get the highlighting with something as simple as 

=MONTH(TODAY()) = COLUMN() - 2

Where -2 is of course used because January starts in column 3.