Does excel sense a formula in the cell above without the formula being pasted

[salvis toscano]

in A1 i enter 6, in A2 i enter 7, in A3 i enter 8, in A4 i enter 5

in B1 i enter 2, in B2 i enter 2, in B3 i enter 3, in B4 i enter 4

in C1 i enter the formula, =A1+B1

i copy the formula down by dragging from C1 to C4.

then i enter 10 in A5, and 11 in B5.

C5 automatically shows A5+B5=21 without the formula being dragged or pasted into C5.

how did this happen?

i have not dragged the formula. i have not pasted the formula.i have not entered A5+B5 in C5. Still, C5 gives the correct answer A5+B5.

this happens only if the formula has been dragged for 4 consecutive rows.

the 5th row then automatically senses the formula.

this does not happen in c4 if the formula is dragged for 3 rows.

how does excel sense the formula in the 5th row, if the formula has been dragged in the previous 4 rows?

what is the answer to this strange behaviour in excel.

i am using excel 2003.

 

[xld]

This is an Excel setting, Extend data range formats and formulas.


You can find it in Excel Options>Advanced, in the Editing Options section.

 

[salvis toscano]

hi xld,


thanks a million.


i never knew that such an option existed.


i unchecked the option and the formula stopped at the cell to where it was dragged.


thanks once again for this very valuable information.