Results 1 to 4 of 4
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    ??Avoid a formula error?? (Excel2000>)

    Lets say I have values in A1,A5 and A10 on sheet 1, and values in A1, A5 and A10 on sheet 2.
    On sheet 3 I created a 3D formula to sum all vals. ie =Sheet1!A1+Sheet1!A5+Sheet1!A10+Sheet2!A1+Sheet2!A 5+Sheet2!A10.
    How do I avoid the error occuring if I delete row 5 on Sheet1? The error is viewed as Sheet1!#REF as the reference is removed to A5 on sheet 1.
    I notice that if I had used SUM Functions and ranges it does not occur as the ranges are updated. But I can't use ranges as the values to sum are non adjacent cells!
    Any suggestions?
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ??Avoid a formula error?? (Excel2000>)

    This has nothing to do with having multiple worksheets. The same problem occurs if you have =A1+A5+A10 on a single worksheet. The best solution is not to delete the row, of course <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    A kludgy workaround uses the ISERROR function:

    =IF(ISERROR(Sheet1!A1),0,Sheet1!A1)+IF(ISERROR(She et1!A5),0,Sheet1!A5)+IF(ISERROR(Sheet1!A10),0,Shee t1!A10)+IF(ISERROR(Sheet2!A1),0,Sheet2!A1)+IF(ISER ROR(Sheet2!A5),0,Sheet2!A5)+IF(ISERROR(Sheet2!A10) ,0,Sheet2!A10)

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ??Avoid a formula error?? (Excel2000>)

    <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    I was kinda expecting this answer, but just maybe hoping and dreaming of a less tedious method. It is pretty obvious that excel cannot really do much else other that inform you the reference is deleted, so I understand its reaction.
    Your answer atleast confirms my suspicions!!!
    Thanx Hans
    <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,
    Rudi

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ??Avoid a formula error?? (Excel2000>)

    It depends on what you want the formula to do when you delete row 5. If you want it to add A1, the new A5, and the new A10 on Sheet2, then you could change the formula to:

    <pre>=INDIRECT("Sheet1!A1")+INDIRECT("Sheet1!A5")+ INDIRECT("Sheet1!A10")+INDIRECT("Sheet2!A1")+INDIR ECT("Sheet2!A5")+INDIRECT("Sheet2!A10")
    </pre>

    Legare Coleman

Posting Permissions

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