Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    10

    Unanswered: If formula (Scratching my brain)

    Is it possible to do the following?

    I am working on workbook trying to create a template for a friend. What I am trying to do is keep sheet1 as the finished sheet, and have sheets 2 & 3 as the informatin log sheets. Here is the formula that I started with: =IF('2009'!$B$2=20, 2*10,""). What he wants is to have sheet1 showing his clients, months, & totals. On sheet2 he wants to be able to input the amount of the payment every month in one cell (i.e. 20 for Jan, 40 for Feb, 60 for Mar., etc.). I have the formula above in the month of January, and have this for the remainder: =IF('2009'!$B$2=40, 40/2,"") this way on sheet1 all months show $20.00. The 40 is changed to 60 and 80 and so on for every month. Now to the question.

    How can I use this formula and still keep sheet1 showing $20.00 for all months, no matter what the cell on sheet2 has in it, but the months that have not been paid yet show a zero balance?

    Hope this is not too confusing.

    Thanks

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Fire Alarm,

    I'm having a little bit of trouble trying to envisage your set-up. Would it be possible for you to zip and attach an example workbook?

    Thanks

  3. #3
    Join Date
    Jul 2009
    Posts
    10
    Colin, please see attached.
    Attached Files Attached Files

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    I'm still struggling a bit with this one.


    Let's take client "T" as a specific example.

    Currently the 2009 payment is $120. How do you want the summary Sheet1 to look? What should happen if the payment is subsequently changed to $280?

  5. #5
    Join Date
    Jul 2009
    Posts
    10
    If the amount on 2009 tab is changed from 120 to 140 for the next month, then $20 should remain in June and $20 should be added to July. The amount should not go over $240

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    Sorry, I want to help but I just don't get this at all.

    The 2009 and 2010 tabs seem to be an accrued total payment.
    So he would have to look in the relevant cell for the client and then add $20 to it if a new payment has come in - is that right? So, change $120 to $140. And then you want that reflected on Sheet1?

    But how is that better for him than just having Sheet1 (remove the others) and just to type $20 in under the correct date?

    Hopefully someone else understands this better and will be able to step in...

Posting Permissions

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