Locking portions of a formula

[tiggatattoo]

Hi,
I am looking to find out how to lock a specific cell in a formula.

for example: when I copy and paste a formula, it automatically grabs
the next cell in line in the column.

In the same row I would like to keep two cells locked so it only
changes the column heading not the row.

I have about 100 or so columns to do this for and 4,000 rows.
How can I copy and paste or paste special or something to get this to
work without manually typing the formula for each cell. See example
below.

ex: column BM Row 2 formula =if(BM1=BK2,BL2,0)
column BM Row 3 formula =if(BM1=BK3,BL3,0)
column BM row 4 formula =if(BM1=BK4,BL4,0)
column BN Row 2 formula =if(BN1=BK2,BL2,0)
column BN Row 3 formula =if(BN1=BK3,BL3,0)
column BN Row 4 formula =if(BN1=BK4,BL4,0)
column BO Row 2 formula =if(BO1=BK2,BL2,0)
column BO Row 3 formula =if(BO1=BK3,BL3,0)
column BO Row 4 formula =if(BO1=BK4,BL4,0)


Thanks Candie

 

[Guest]

You can lock the column that you need using the $ sign before it. For example:
column BM Row 2 formula =if(BM1=$BK2,$BL2,0)
When you copy this formula, the reference to the columns Bk and BL won't
change.

Hope this helps,
Miguel.

 

[David Biddulph]

"tiggatattoo" <[email protected]>
wrote in message

Hi,
I am looking to find out how to lock a specific cell in a formula.

for example: when I copy and paste a formula, it automatically grabs
the next cell in line in the column.

In the same row I would like to keep two cells locked so it only
changes the column heading not the row.

I have about 100 or so columns to do this for and 4,000 rows.
How can I copy and paste or paste special or something to get this to
work without manually typing the formula for each cell. See example
below.

ex: column BM Row 2 formula =if(BM1=BK2,BL2,0)
column BM Row 3 formula =if(BM1=BK3,BL3,0)
column BM row 4 formula =if(BM1=BK4,BL4,0)
column BN Row 2 formula =if(BN1=BK2,BL2,0)
column BN Row 3 formula =if(BN1=BK3,BL3,0)
column BN Row 4 formula =if(BN1=BK4,BL4,0)
column BO Row 2 formula =if(BO1=BK2,BL2,0)
column BO Row 3 formula =if(BO1=BK3,BL3,0)
column BO Row 4 formula =if(BO1=BK4,BL4,0)

Try
=if(BM$1=$BK2,$BL2,0)

I suggest you look at the help on absolute and relative addressing.