Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Location
    London UK
    Posts
    8

    Unanswered: Basic query from novice

    Hi all,

    I am trying to create my first spreadsheet and have hit a wall.

    I am trying to get the balance column to automatically adjust itself after any entries are made in either the credit or debit columns.

    Also, if I enter say, 100 in the credit column, I want to avoid having to enter 0 in the debit cell next to it in order for the sum to work.

    I would also like to have a clean looking balance column. By that, I mean I do not want the cells in the balance column to show anything until they are actually in use. <<< Did that make sense?


    Thanks in advance,

    Andy
    Last edited by SirTfireball; 02-20-04 at 09:43.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Welcome to the board!

    Put headings in the following cells:

    B2 - Credit
    C2 - Debit
    D2 - Balance

    Then in cells B3 and C3 enter 0. In D2 enter the beginning amount

    in D3, enter this formula:

    =IF(AND(B3=0)*(C3=0),"",D2+C3-B3)

    [Edited: forgot about "not showing anything until credit/debit entered, so added the IF statement]

    Then copy this cell down column D as far as you want to go. Any change made to credit or debit will appear in the resulting balance.

    (Adjust formula to reflect how you understand debit/credit.)
    Last edited by shades; 02-20-04 at 11:44.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Feb 2004
    Location
    London UK
    Posts
    8
    Thanks for the welcome.

    I'm afraid your kind attempt to help me has flown straight over my head. I am quite the moron don't ya know )

    I am attempting to add a link to the sheet. Hope it works.
    Attached Files Attached Files

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Not sure what happened. I added a sheet to your file, and put in the formula that works fine. (I put the formula in column H for you to see how column G is set up)
    Attached Files Attached Files
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Feb 2004
    Location
    London UK
    Posts
    8
    I have added a new sheet which shows how I want it to look. I add to the credit/debit columns and the balance column auto adjusts.

    I am truly sorry if I am being very dense, unclear or both Your explanation goes way over my head.

    Patiently yours,

    Dumbo
    Attached Files Attached Files

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    BTW, no need to apologize. We work together on this.

    Okay, I further looked at your sheet. Columns E and F are not "blank" even though it looks that way. There is a space in every cell in those columns. Thus, my formulas would not detect that.

    So go into columns E and F starting at row 4, and clear contents (select the cells, right-click and choose "Clear Contents").

    Copy this formula into G5:

    =IF(AND(E5="")*(F5=""),"",G4-F5+E5)

    Copy this down column G.

    Be sure that when you copy from the message board that there is no extra space after that closing parenthesis.

    If you need more help, post back.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  7. #7
    Join Date
    Feb 2004
    Location
    London UK
    Posts
    8
    Thanks heaps

  8. #8
    Join Date
    Feb 2004
    Location
    London UK
    Posts
    8
    Originally posted by SirTfireball
    Thanks heaps
    I have now got this far......

    but I would like to do away with having to enter 0 rather than just leave the cell empty and I would prefer not to have all those cells showing #VALUE!. Can this be done?
    Attached Files Attached Files

  9. #9
    Join Date
    Oct 2003
    Posts
    1,091
    Using that latest formula should get rid of the #VALUE! in the cells. That is the purpose of the IF statement. If both E5 and F5 are empty, then it will enter "" (which means it leaves it blank).
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  10. #10
    Join Date
    Oct 2003
    Posts
    1,091
    Okay, here is the updated one. To get the format the same for all of Column E, F, and G. Select G4, then click the Format Painter button on the toolbar (looks like a paintbrush. Then select all the cells you want the same way (even if they don't have any formulas or numbers).

    Now, check this attachment.
    Attached Files Attached Files
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  11. #11
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    Welcome to the Board!

    See: http://office.microsoft.com/template...ister&Scope=TC for a pre-packaged Checkbook Register from Microsoft.

    Hope that helps,

    Smitty

    (Heya Shades!)

  12. #12
    Join Date
    Feb 2004
    Location
    London UK
    Posts
    8
    Thank you, thank you, thank you.......

    You've made an old man very happy and I never felt a thing

  13. #13
    Join Date
    Oct 2003
    Posts
    1,091
    Glad it worked. And thanks for posting back.

    Hey, Smitty. It's been hectic, so I've been scarce around the boards.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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