Conditional Formatting applying to old columns when new columns are copied and inserted.

%3CLINGO-SUB%20id%3D%22lingo-sub-2412309%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20applying%20to%20old%20columns%20when%20new%20columns%20are%20copied%20and%20inserted.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2412309%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20columns%20that%20have%20conditional%20formatting%20applied%20using%20a%20CONCATENATE%20formula.%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20copy%20and%20paste%20the%20new%20columns%2C%20the%20formula%20still%20uses%20the%20old%20columns%20and%20doesn't%20pick%20up%20the%20new%20ones.%20Please%20can%20someone%20help%3F%20I%20have%20attached%20a%20sample%20file.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20columns%20AK%20%26amp%3B%20AL%20are%20picking%20up%20columns%20AN%20%26amp%3B%20AO%20when%20I%20copy%20the%20three%20columns.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22milo1234_0-1622724969836.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F285926i3247404ACFD33D12%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22milo1234_0-1622724969836.png%22%20alt%3D%22milo1234_0-1622724969836.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2412309%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2412892%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20applying%20to%20old%20columns%20when%20new%20columns%20are%20copied%20and%20inserted.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2412892%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F972444%22%20target%3D%22_blank%22%3E%40milo1234%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20guess%20you%20need%20to%20unlock%20columns%20and%20use%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DVLOOKUP(CONCATENATE(AE3%2CAF3)%2CLegend!%24C%242%3A%24D%2438%2C2%2CFALSE)%3D%22G%22%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2412955%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20applying%20to%20old%20columns%20when%20new%20columns%20are%20copied%20and%20inserted.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2412955%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnfortunately%20when%20I%20do%20this%20and%20take%20out%20the%20locking%20key%20%24%2C%20the%20conditional%20formatting%20does%20not%20work.%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2415392%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20applying%20to%20old%20columns%20when%20new%20columns%20are%20copied%20and%20inserted.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2415392%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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. 

 

milo1234_0-1622724969836.png

 

3 Replies

@milo1234 

I guess you need to unlock columns and use

=VLOOKUP(CONCATENATE(AE3,AF3),Legend!$C$2:$D$38,2,FALSE)="G"

@Sergei Baklan 

Unfortunately when I do this and take out the locking key $, the conditional formatting does not work. 

Please see attached.