how do i prevent a #REF! error when i delete non adjacent rows?
 

I set up a simple function, e.g., =sum(B2,B5,B7,B9). If I delete rows 5, 6
and 7, the cell that used to give me the total now shows #REF! My question
is how can I delete the rows and still get the sum for adding B2 and
B9--without having to start over and select cells B2 and B9.

Assuming you have in say, D20: =SUM(B2,B5,B7,B9)
and you want always to point the summation to cells: B2,B5,B7,B9, then one
way ..

Put instead in D20, and array-enter
(i.e. press CTRL+SHIFT+ENTER):

=SUM(TRANSPOSE(OFFSET(INDIRECT("B1"),{1;4;6;8},)))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"ChuckSF" wrote in message
...
I set up a simple function, e.g., =sum(B2,B5,B7,B9). If I delete rows 5,
6
and 7, the cell that used to give me the total now shows #REF! My
question
is how can I delete the rows and still get the sum for adding B2 and
B9--without having to start over and select cells B2 and B9.