Lock Reference so it never changes

[ParanoidAndroid]

Hi Guys

I want to lock the K:K in this line so it never changes

=IF(ISERROR(MATCH(V2,'3056_LCS'!K:K,0))

How do I do this?

 

[xenou]

Simplest is to add $ characters which keep the reference to K if you drag or copy the cells:
=IF(ISERROR(MATCH(V2,'3056_LCS'!$K:$K,0))

The reference will still change if you cut columns. Often that is desirable behavior. Otherwise, you could look into using INDIRECT(). I think something could even be done with INDEX() here too.

 

[CWatts]

Just put a $ in front of each K so K:K would become $K:$K.

manipulating these reference locks is a very handy trick so practice with them.

You can lock a Column, but let the row number change with =$K2, or lock a row but let a column change with =K$2.

 

[ParanoidAndroid]

Thanks Guys

Yes, I cut columns..so that explains it.

The K:K is anticipated once all the columns are cut..

How do i used the indirect or index?

 

[xenou]

This should work:

=IF(ISERROR(MATCH(V2,INDIRECT("3056_LCS!$K:$K"),0))

There will be a performance hit using Indirect(), but it won't matter if your workbook is not heavy with calculations.