How can I get Excel to stop changing my formulas?

V

vehl

I have a formula CountA(E5:p5). I want to be able to add a column i
front of E and have my formula remain the same. At present the formul
changes to CountA(F5:Q5). Does anyone know how to do this
 
M

Myrna Larson

Both of these formulas will count 12 cells beginning at E5. However, if you
add a column in front of E, the values in E5:p5 move to F5:Q5. If there are 12
entries, after inserting the new column, the formula gives 11, because E5 is
now empty, and the formula doesn't include Q5. If you put a value in the new
E5, you'll get 12 again.

=COUNTA(OFFSET($A$1,4,4,1,12))
=COUNTA(INDIRECT("$E$5:$P$5"))
 
Top