Jun 11 2021 01:46 AM
I have columns that have conditional formatting applied using a CONCATENATE formula.
When I copy and paste the new columns, the formula still uses the old columns and doesn't pick up the new ones. Please can someone help? I have attached a sample file.
For example, columns AK & AL are picking up columns AN & AO when I copy the three columns.
Jun 11 2021 03:19 AM
See if this works better for you. I replaced all rules with a set of 6 rules for the entire range.
Jun 11 2021 03:51 AM
Jun 11 2021 04:08 AM - edited Jun 11 2021 05:17 AM
This is the layout of my spreadsheet and I have attempted to use your 6 rule, however this is not working. Please can you apply to the layout attached and I will mark as best response. Thank you Hans
Jun 11 2021 04:14 AM
The formula refers to A3, not to $A3, so Excel will dynamically adjust it in other columns.
The formula first uses MOD(COLUMN(...),3) to check whether the cell is in a column that is to be formatted conditionally.
If so, it uses OFFSET in combination with MOD(COLUMN(...),3) to determine the two cells to be concatenated. For a cell in column A (or D or G etc.), it is the cell itself and the cell to the right.
For a cell in column B (or E or H etc.) is the cell itself and the cell to the left.
This makes the formula much more dynamic.
Jun 11 2021 04:36 AM
Originally, the range to be formatted began in column A = column 1. 1 = 1 Mod 3.
Now it begins in column I = column 9. 9 = 0 Mod 3.
So everything shifts.
Jun 11 2021 05:27 AM
Thank you - this makes sense.
I have applied the formulas to the last column, however there are only a few cells which it does not apply to. Please find attached.
Jun 11 2021 05:30 AM
Which cells, for example?
Keep in mind that you have applied direct formatting to some of the cells. If none of the 6 rules applies, you'll see the direct formatting.
Jun 11 2021 05:54 AM
Jun 11 2021 06:02 AM
Please go back to the workbook that I attached. You have somehow managed to completely mess up the rules.
Jun 11 2021 06:26 AM
Jun 11 2021 06:38 AM
I3 must be the active cell in the selection when you create the rules. In your most recent workbook, this is what I see if I select Manage Rules and edit one of the rules:
=IF(MOD(COLUMN(XDW3),3)<>2,VLOOKUP(CONCATENATE(OFFSET(XDW3,0,-MOD(COLUMN(XDW3),3)),OFFSET(XDW3,0,1-MOD(COLUMN(XDW3),3))),Legend!$G$2:$H$38,2,FALSE)="Blank")
As you see, it does not refer to I3 but to XDW3.
Jun 11 2021 07:21 AM
Jun 11 2021 08:22 AM
Jun 11 2021 08:26 AM
Jun 11 2021 08:54 AM
Jun 11 2021 08:57 AM
@Hans Vogelaar No problem, please find attached, thanks.
Jun 11 2021 08:59 AM
I'll look at it when I get back.
Jun 11 2021 11:56 AM
I see the problem, but the only conclusion I can draw is that Excel can't handle formatting so many cells. The problem only occurs in some rows of some columns. I have checked the formulas; they return the correct values for the problem cells and yet Excel applies the rules incorrectly...
Jun 15 2021 04:59 AM