Thanks:  0
Likes:  0

1. I am trying to delete rows without loosing totals.
For example:
If I use @sum in cell 'A6' to find the total of cells 'A1:A5' and then delete rows 'A1:A5', an error message appears i.e. '#REF!'. Is there a way that I can delete the rows and still leave the total intact. This used to be possible in LOTUS123 but I can't find a way to do it in excel. I am guessing there must be a formula I can use when adding the cells or perhaps some way of formatting the cell with the total in it. Any help would be appreciated!

2. In b6 you can type =if(a6>0,a6,b6) this will hold the value that was in a6 after you have deleted a1:a5.

Denny

[ This Message was edited by: kinkyparamour on 2002-03-24 15:47 ]

[ This Message was edited by: kinkyparamour on 2002-03-24 15:54 ]

3. On 2002-03-24 15:44, kinkyparamour wrote:
In b6 you can type =(if a6>0,a6,b6) this will hold the value that was in a6 after you have deleted a1:a5.

Denny

[ This Message was edited by: kinkyparamour on 2002-03-24 15:47 ]
Hi kinkyparamour:
I don't think that would work ... because whatever is in A6 or B6 is dependent on what was in a1:A5.
What you have to do Billmalc, convert the formula in A6 to a static value. Click on A6 then EDIT|COPY and then EDIT|COPY|PASTE_SPECIAL|Values
then even when reference to A1:A5 is gone, the value in A6 stays.
HTH

Please post back if it works for you ... otherwise explain a little further and let us take it from there!

_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard coded signature

[ This Message was edited by: Yogi Anand on 2003-01-19 15:22 ]

4. As long as a1:a5 is set the formula will work. if A1:A6 changes to say A1:a8 then it will not.........

Denny

5. Hi Guys

Thanks very much for the prompt replies. The idea from Denny did not work (although it may have been me who did something wrong!). The suggestion from Yogi works perfectly!! Hooray!! I spent two days looking at help menus and the microsoft site and you helped me in about 20 minutes. Thanks again to you both and of course to MrExcel !!

6. Hi Billmalc

You could also nest the INDIRECT function into the SUM function, eg

=SUM(INDIRECT("A1:A100"))

This will allow you to delete any cells and/or rows from the range without the SUM reference being effected.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•