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

G

Guest

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.
 
M

Max

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},)))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top