autofill formula

[minmin00]

how can i auto fill a range while leaving the cell number the same?

=IF(AND(B4>=c2,b4<=c3),"Yes","No")

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 ray:
[TABLE="width: 500"]
<tbody>[TR]
[TD]a1[/TD]
[TD]a2[/TD]
[TD]a3[/TD]
[TD]a4[/TD]
[TD]a5[/TD]
[TD]a6[/TD]
[TD]a7[/TD]
[TD]a8[/TD]
[TD]a9[/TD]
[/TR]
[TR]
[TD]b1[/TD]
[TD]b2(Empty)[/TD]
[TD]b3(Empty)[/TD]
[TD]b4(800)[/TD]
[TD]b5(1000)[/TD]
[TD]b6(1200)[/TD]
[TD]b7(1500)[/TD]
[TD]b8(1700)[/TD]
[TD]b9(2000)[/TD]
[/TR]
[TR]
[TD]c1[/TD]
[TD]c2(800)[/TD]
[TD]c3(900)[/TD]
[TD]c4(blank- formula to be filled)[/TD]
[TD]c5(blank- formula to be filled)[/TD]
[TD]c6(blank- formula to be filled)[/TD]
[TD]c7(blank- formula to be filled)[/TD]
[TD]c8(blank- formula to be filled)[/TD]
[TD]c9(blank- formula to be filled)[/TD]
[/TR]
[TR]
[TD]d1[/TD]
[TD]d2(900)[/TD]
[TD]d3(1200)[/TD]
[TD]d4(blank- formula to be filled)[/TD]
[TD]d5(blank- formula to be filled)[/TD]
[TD]d6(blank- formula to be filled)[/TD]
[TD]d7(blank- formula to be filled)[/TD]
[TD]d8(blank- formula to be filled)[/TD]
[TD]d9(blank- formula to be filled)[/TD]
[/TR]
[TR]
[TD]e1[/TD]
[TD]e2(1700)[/TD]
[TD]e3(1900)[/TD]
[TD]e4(blank- formula to be filled)[/TD]
[TD]e5(blank- formula to be filled)[/TD]
[TD]e6(blank- formula to be filled)[/TD]
[TD]e7(blank- formula to be filled)[/TD]
[TD]e8(blank- formula to be filled)[/TD]
[TD]e9(blank- formula to be filled)[/TD]
[/TR]
[TR]
[TD]f1[/TD]
[TD]f2(800)[/TD]
[TD]f3(1200)[/TD]
[TD]f4(blank- formula to be filled)[/TD]
[TD]f5(blank- formula to be filled)[/TD]
[TD]f6(blank- formula to be filled)[/TD]
[TD]f7(blank- formula to be filled)[/TD]
[TD]f8(blank- formula to be filled)[/TD]
[TD]f9(blank- formula to be filled)[/TD]
[/TR]
[TR]
[TD]g1[/TD]
[TD]g2(1000)[/TD]
[TD]g3(1100)[/TD]
[TD]g4(blank- formula to be filled)[/TD]
[TD]g5(blank- formula to be filled)[/TD]
[TD]g6(blank- formula to be filled)[/TD]
[TD]g7(blank- formula to be filled)[/TD]
[TD]g8(blank- formula to be filled)[/TD]
[TD]g9(blank- formula to be filled)[/TD]
[/TR]
[TR]
[TD]h1[/TD]
[TD]h2(1200)[/TD]
[TD]h3(1300)[/TD]
[TD]h4(blank- formula to be filled)[/TD]
[TD]h5(blank- formula to be filled)[/TD]
[TD]h6(blank- formula to be filled)[/TD]
[TD]h7(blank- formula to be filled)[/TD]
[TD]h8(blank- formula to be filled)[/TD]
[TD]h9(blank- formula to be filled)[/TD]
[/TR]
[TR]
[TD]j1[/TD]
[TD]j2(1800)[/TD]
[TD]j3(2000)[/TD]
[TD]j4(blank- formula to be filled)[/TD]
[TD]j5(blank- formula to be filled)[/TD]
[TD]j6(blank- formula to be filled)[/TD]
[TD]j7(blank- formula to be filled)[/TD]
[TD]j8(blank- formula to be filled)[/TD]
[TD]j9(blank- formula to be filled)[/TD]
[/TR]
[TR]
[TD]k1[/TD]
[TD]k2(800)[/TD]
[TD]k3(2000)[/TD]
[TD]k4(blank- formula to be filled)[/TD]
[TD]k5(blank- formula to be filled)[/TD]
[TD]k6(blank- formula to be filled)[/TD]
[TD]k7(blank- formula to be filled)[/TD]
[TD]k8(blank- formula to be filled)[/TD]
[TD]k9(blank- formula to be filled)[/TD]
[/TR]
[TR]
[TD]l1[/TD]
[TD]l2(1200)[/TD]
[TD]l3(1300)[/TD]
[TD]l4(blank- formula to be filled)[/TD]
[TD]l5(blank- formula to be filled)[/TD]
[TD]l6(blank- formula to be filled)[/TD]
[TD]l7(blank- formula to be filled)[/TD]
[TD]l8(blank- formula to be filled)[/TD]
[TD]l8(blank- formula to be filled)[/TD]
[/TR]
</tbody>[/TABLE]

 

[vlady]

using $

A$1 - the number 1 will not change - letter will change

$A1 - letter will change - number will not change

$A$1 - both letter and number will not change

hope this is what you want

 

[minmin00]

using $

A$1 - the number 1 will not change - letter will change

$A1 - letter will change - number will not change

$A$1 - both letter and number will not change

hope this is what you want

yes, this is partially what i wanted... not only i want to autofill to the bottom, i also want to autofill to the left which i cannot do it simultaneously

 

[vlady]

C4=IF(AND(B4>=$c$2,b4<=$c$3),"Yes","No") - horizontal

D4=IF(AND($B$4>=D2,$b$4<=D3),"Yes","No") - vertical

you have to edit the first rows of formula then fill down

 

[minmin00]

i still dont quite understand, do you mean i have to put 2 formulas one in c4 to auto fill the row and one in d4 to autofill the column, than what about the rest?
and i dont know how many columns i have
sorry if my question is really stupid...

 

[vlady]

you can not do it simultaneously.
try it step by step

in C4=IF(AND($B$4>=c2,$b$4<=c3),"Yes","No") fill to the right--->>>up to cell G4

then you have to edit those formulas one by one -get rid of the Dollar sign of $B$4 then put dollar sign on $C$2 and $C$4

becomes C4=IF(AND(B4>=$c$2,b4<=$c$3),"Yes","No") then you can drag down the formulas,

 

[chetanmehra1989]

Try


=IF(AND($B4>=C$2,$B4<=C$3),"Yes","No")

 

[minmin00]

you can not do it simultaneously.
try it step by step

in C4=IF(AND($B$4>=c2,$b$4<=c3),"Yes","No") fill to the right--->>>up to cell G4

then you have to edit those formulas one by one -get rid of the Dollar sign of $B$4 then put dollar sign on $C$2 and $C$4

becomes C4=IF(AND(B4>=$c$2,b4<=$c$3),"Yes","No") then you can drag down the formulas,

because i needed it to be in a macro and the number of columns may vary, so are there anyways to do it dynamically?