Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: flexible formulas

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

    Default

    Tough problem for me. Is it possible to have
    a formula that is some what self adapting.
    I will explain what I mean. I have a list
    of numbers. cells a1-a9. cell a10 has the
    formula =a1+a2+a3+a4+a5+a6+a7+a8+a9
    Assume the formula must be written with each cell noted. The actually set up is with multiple pages, formulas, cells etc.

    Problem. If I delete a line, say a4 the formula does not work. ref# is shown in a10
    as well as in the formula itself. Is there a
    way to create the formula so that if I delete
    a line, the formula will simply calculate the
    remaining cells.(=a1+a2+a3+a5+a6+a7+a8+a9)

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default


    =SUM(A1:A9)

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

    Default

    This will not work. As I mentioned, each cell needs to be listed. If I then delete the line can the formulated cell automatically adjust for the missing cell?

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-11 22:49, cpc wrote:
    This will not work. As I mentioned, each cell needs to be listed. If I then delete the line can the formulated cell automatically adjust for the missing cell?
    "delete the line" means deleting a row, is it not?

    Suppose you delete row 4. Are you requiring that the formula that you use

    =a1+a2+a3+a5+a6+a7+a8+a9

    stays the same, without giving error?

    And

    =SUM(A1:A9)

    does not change to

    =SUM(A1:A8) ?



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

    Default

    Aladin,

    If I understand your comment, the answer is yes. Assume you can not use the formula
    =sum(a1:a9) You must list each cell in the formula. ie a1+a2+a3 and so on. When you
    delete a row, and the formula now has a ref#
    instead of the a4 ie the deleted row, can the formula ignore this ref# and still calculate
    the remaining cells listed in the formula..

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    Assume you can not use the formula
    =sum(a1:a9) You must list each cell in the formula. ie a1+a2+a3 and so on. When you
    delete a row, and the formula now has a ref#
    instead of the a4 ie the deleted row, can the formula ignore this ref# and still calculate the remaining cells listed in the formula.


    No. But, why do you need to sum a range using the + operator that requires its operands to be explicitly listed?

    Aladin

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

    Default

    I understand exactly what you mean. I have a spreadsheet littered with this type of problem.

    I was able to get each page looking exactly the same and use the following formula:

    =sum(sheet1:sheet3!A1)

    Is this or a variant of possible in your spreadsheet?

    I do not beleive that using the "+" operator is allows a dependant to be deleted without giving a #ref error. However, similar formulas that will. I'm still pondering another solution.

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

    Default

    Aladin,

    This type of formula needs to be used because in actuality the formula may pick up every other cell, not a1:a9, or a group of numbers and then a few single cells, or totals from
    say 25 pages, when I want to delete say the last four pages. This maybe another way of looking at the problem. I have lists of numbers as described, and a total. (the formula in question) on say 49 pages, with page 50 being a grand total of all 49 pages.
    If I delete say pages 40-49, asuume they are not being used, the formula on page 50 no longer works. Unless I re - formulate the cell. Can this formula on page 50 be written to allow for the deletion of pages.

    croweld89 look at the above. Do you think your idea will work here?

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

    Default

    Let's divide by 10 pages...

    Say sheets 1 - 4 are populated and sheet 5 gives a grand total summary of the previous pages. If (and the big if), you can make sheets 1 - 4 the same, then this will work.

    For example, we put "5" in cell D4 on sheets 1 - 4. Then on sheet 5 in cell B11 (or whatever cell you need) place the formula:

    =sum(sheet1:sheet4!D4)

    This will allow you to delete all but one sheets without receiving an error. This requires all formulas on sheet 5 to pull data that are only in the same cell on all 4 sheets. Let me know if this is possible.

    There should be an alternative combining the index/match formulas, however, I have not adepted this formula yet.

    -Dave

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    WA state
    Posts
    409
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I don't see any way around manually editing your formulas. If possible, build your worksheets so that you won't have to delete the references that your formulas are built around.

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
  •