Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula Troubles (Excel 2003/SP2)

    Sorry to fast readers! I lost the attachment when I spell-checked.

    Okay, I'm hoping it is just because it is so late in the day that these related formulas are eluding me. I had the first one working (yellow in the attached sheet), but only for the first total and not the others. And I couldn't get any of the rest. I found it difficult to explain in writing, so you'll need to look at the attached sheet, where I have entered the formulas by manually selecting the cells to be included in the totals and indicated via text what I need the formulas to do automatically.

    I had something like this:

    {SUM(IF(LEFT(R2C1:$R24C1)=RC1&"-",Requested,0),0)}

    for the first formula, but it results in a circular reference. It seems to get the right total, but only the first time I try to use it.

    The most important formula I need is the one in blue. If we have to do the others manually, the user can live with that. It is the third one that is a pain, because it changes the most frequently. Many thanks!

    --Karyl
    Attached Files Attached Files

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

    Re: Formula Troubles (Excel 2003/SP2)

    Take the "subtotals" out of the table, that avoids the circular references. See attached version.
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Portland, Maine, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Troubles (Excel 2003/SP2)

    The circular ref is in C2. You use the range name 'requested' in your formula and 'requested' includes C2.

    Here is my initial thought. Simplify it just a little bit. For the formulas in C & D, just use a sum formula for each of the codes (600, 650, etc). As you insert rows for additional requests or spending, the fomulas will expand themselves. Then an array formula in E gets the right result.
    Attached Files Attached Files

  4. #4
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Troubles (Excel 2003/SP2)

    I don't know why it always seems so easy after I see how you do it, yet it always seems so difficult when I try to do it myself! Thanks so much for the options! My user is up and running and very happy with the resulting simplicity vs. her manual method of the past.

    --Karyl

Posting Permissions

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