auto fill a range while leaving the cell number the same

[wlmlimin]

Please Login or Register  to view this content.

the horizontal cells(ie. a4,c5,c6,c7,c8,c9), with the formula,cells c2 and c3 will remain the same
C4=IF(AND(B4>=c2,b4<=c3),"Yes","No")
C5=IF(AND(B5>=c2,b5<=c3),"Yes","No")
C6=IF(AND(B6>=c2,b6<=c3),"Yes","No")
C7=IF(AND(B7>=c2,b7<=c3),"Yes","No")

for the vertical cells(ie. c4,d4,e4,f4,g4), cell b4 will remain the same
D4=IF(AND(B4>=D2,b4<=D3),"Yes","No")
E4=IF(AND(B4>=E2,b4<=c3),"Yes","No")
E5=IF(AND(B4>=F2,b4<=F3),"Yes","No")
E6=IF(AND(B4>=G2,b4<=G3),"Yes","No")
E7=IF(AND(B4>=H2,b4<=H3),"Yes","No")

this needs autofill as the table can be very big.. help
a1 a2 a3 a4 a5 a6 a7 a8 a9
b1 b2(Empty) b3(Empty) b4(800) b5(1000) b6(1200) b7(1500) b8(1700) b9(2000)
c1 c2(800) c3(900) c4(blank- formula to be filled) c5(blank- formula to be filled) c6(blank- formula to be filled) c7(blank- formula to be filled) c8(blank- formula to be filled) c9(blank- formula to be filled)
d1 d2(900) d3(1200) d4(blank- formula to be filled) d5(blank- formula to be filled) d6(blank- formula to be filled) d7(blank- formula to be filled) d8(blank- formula to be filled) d9(blank- formula to be filled)
e1 e2(1700) e3(1900) e4(blank- formula to be filled) e5(blank- formula to be filled) e6(blank- formula to be filled) e7(blank- formula to be filled) e8(blank- formula to be filled) e9(blank- formula to be filled)
f1 f2(800) f3(1200) f4(blank- formula to be filled) f5(blank- formula to be filled) f6(blank- formula to be filled) f7(blank- formula to be filled) f8(blank- formula to be filled) f9(blank- formula to be filled)
g1 g2(1000) g3(1100) g4(blank- formula to be filled) g5(blank- formula to be filled) g6(blank- formula to be filled) g7(blank- formula to be filled) g8(blank- formula to be filled) g9(blank- formula to be filled)
h1 h2(1200) h3(1300) h4(blank- formula to be filled) h5(blank- formula to be filled) h6(blank- formula to be filled) h7(blank- formula to be filled) h8(blank- formula to be filled) h9(blank- formula to be filled)
j1 j2(1800) j3(2000) j4(blank- formula to be filled) j5(blank- formula to be filled) j6(blank- formula to be filled) j7(blank- formula to be filled) j8(blank- formula to be filled) j9(blank- formula to be filled)
k1 k2(800) k3(2000) k4(blank- formula to be filled) k5(blank- formula to be filled) k6(blank- formula to be filled) k7(blank- formula to be filled) k8(blank- formula to be filled) k9(blank- formula to be filled)
l1 l2(1200) l3(1300) l4(blank- formula to be filled) l5(blank- formula to be filled) l6(blank- formula to be filled) l7(blank- formula to be filled) l8(blank- formula to be filled) l8(blank- formula to be filled)

[JBeaucaire]

You add $ signs in front of the parts of a cell address that you want to LOCK.

A1
$A1
A$1
$A$1

Once a cell reference is locked in this manner, the parts with the $ symbols will NOT adjust as the cell is copied around, while the parts without the $ signs will adjust.