Deleting Rows

Billmalc

New Member
Joined
Mar 23, 2002
Messages
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!
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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
 
Upvote 0
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
 
Upvote 0
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 !!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top