Jun 23 2021 02:06 AM
Hi Team, I have successfully created a conditional formatting concatenate formula, however when the columns are copied and new ones are inserted, the conditional formatting cells are using the old columns and not basing them on the new columns.
For example: I copy columns AY and AZ into BB and BC, however the formula is picking up AY and AZ still.
I have attempted to resolve this issue numerous times, however I have had no luck. Please find print screens attached.
=VLOOKUP(CONCATENATE($BB3,$BC3),Legend!$G$2:$H$38,2,FALSE)="Blank"
=VLOOKUP(CONCATENATE($BB3,$BC3),Legend!$G$2:$H$38,2,FALSE)="R"
=VLOOKUP(CONCATENATE($BB3,$BC3),Legend!$G$2:$H$38,2,FALSE)="AR"
=VLOOKUP(CONCATENATE($BB3,$BC3),Legend!$G$2:$H$38,2,FALSE)="A"
=VLOOKUP(CONCATENATE($BB3,$BC3),Legend!$G$2:$H$38,2,FALSE)="AG"
=VLOOKUP(CONCATENATE($BB3,$BC3),Legend!$G$2:$H$38,2,FALSE)="G"
Jun 23 2021 05:26 AM
Jun 23 2021 07:04 AM
@mtarler Thank you for your suggestions, however they have not resolved the issue.
The formula I provided showed cells AY and AZ, as I amended this formula, however they originally showed BB and BC when copied and inserted.
The columns either side will not be blank, these are always filled with text however I removed this as they contained sensitive data.
I have attached sample data - please could you have a go and see if you can resolve?
Thank you for your help.
Jun 23 2021 03:28 PM
@milo1234 I created 1 conditional formatting formula you can use as a template:
=VLOOKUP(IFS(LEFT(I$2,3)="W/E",I1&J1,LEFT(H$2,3)="W/E",H1&I1,1,""),Legend!$B$2:$C$38,2,FALSE)="AG"
This was applied to the entire region I:BD and should work as long as the pair of columns you want it applied to are the only columns that start with "W/E".
Jun 24 2021 01:54 AM
@mtarler Thank you for this. I have applied the formula to all relevant columns with the correct colour coding. It seems to work for most of the cells, however in the sample data attached, if I change a cell to F1 and T1 this should be green together, however one cell shows (F1) as green and the other cell as red (T1).
Also when I copy the 3 columns and insert these copied cells for a new week, the cells that the conditional formatting applies to, changes.
Please can you help?
Jun 24 2021 05:31 AM
Solution@milo1234 there was a typo in one of the formulas that references row E instead of H. I fixed that so I hope the attached works for you.
As for copy and paste, yes it will "change" the applied to and the formula but that should be ok. What it is doing is COPYING both the applied to range and formula and shifting the cell references according to the new location. You can see that if under conditional formatting you use the drop-down at the top and set it to "This Worksheet" and you will see 'duplicate rules'. And after a bit of copy, paste, insert, and such that conditional formatting drives my OCD crazy and I have to 'clean it up' but that is cosmetic and not functional issues.
Jun 24 2021 06:43 AM
@mtarler Thank you again, this is really useful.
I can see you resolved the formula, however when you copy columns BH, BI & BJ for a new week and insert the copied cells, the same issue applies.
T1 and F1 does not change to green together - what can I do to stop this?
Jun 24 2021 07:15 AM
Jun 24 2021 07:23 AM
@mtarler Thank you so much for helping, I really appreciate it :)
Jun 24 2021 05:31 AM
Solution@milo1234 there was a typo in one of the formulas that references row E instead of H. I fixed that so I hope the attached works for you.
As for copy and paste, yes it will "change" the applied to and the formula but that should be ok. What it is doing is COPYING both the applied to range and formula and shifting the cell references according to the new location. You can see that if under conditional formatting you use the drop-down at the top and set it to "This Worksheet" and you will see 'duplicate rules'. And after a bit of copy, paste, insert, and such that conditional formatting drives my OCD crazy and I have to 'clean it up' but that is cosmetic and not functional issues.