Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Deleting Rows

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Florida
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Florida
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #5
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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