Deleting Rows

Billmalc · Mar 24th, 2002, 04:36 PM

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!

kinkyparamour · Mar 24th, 2002, 04:44 PM

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 ]

Yogi Anand · Mar 24th, 2002, 04:56 PM

Quote:

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 ]

kinkyparamour · Mar 24th, 2002, 05:00 PM

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

Denny

Billmalc · Mar 24th, 2002, 05:25 PM

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 !!

Dave Hawley · Mar 24th, 2002, 07:11 PM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 24th, 2002, 04:36 PM	#1
Billmalc New Member Join Date: Mar 2002 Posts: 2	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!

Mar 24th, 2002, 04:44 PM	#2
kinkyparamour Board Regular Join Date: Feb 2002 Location: Florida Posts: 82	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 ]

Mar 24th, 2002, 05:00 PM	#4
kinkyparamour Board Regular Join Date: Feb 2002 Location: Florida Posts: 82	As long as a1:a5 is set the formula will work. if A1:A6 changes to say A1:a8 then it will not......... Denny

Mar 24th, 2002, 05:25 PM	#5
Billmalc New Member Join Date: Mar 2002 Posts: 2	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 !!

Mar 24th, 2002, 07:11 PM	#6
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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.