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

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.)

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.

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)

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

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.

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?

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).

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.

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!)

Thank you, thank you, thank you.......

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

Glad it worked. And thanks for posting back.

Hey, Smitty. It's been hectic, so I've been scarce around the boards.