Replies

Thanks very much. Would you or someone else be willing to write such a macro for me?
Clark

Thanks, Klaus. The reason it wouldn’t work for me is that I already had an autoexec macro. I changed it to autonew and it worked.
Clark

I can email the issue to you but according to DCI’s FAQ, their newsletters are not supposed to be posted publicly.
Clark

The empty cell has the value of 0, but unless I put in 0, Excel ignores the cell. So, if I have a column of 10 cells, the first six (A1 through A6) have values, the seventh (A7) is empty, and A8, A9, and A10 have values, if I want to add the column by putting an autosum formula in A11, Excel only gives me the sum of cells A8 through A10, whereas I want the sum of all cells (A1 through A10).
Clark

Say I use this formula for a running total and fill it down for a number of rows, then put in values in the B or C columns, is there a way to insert a new row in the middle of the column without throwing off the balance in the last cell of the D column?

Edited by gwhitfield on 18-Jan-02 06:45.

Hyperlinks added

OK, the Excel file with my workbook in it is at http://soli.inav.net/~ckenyon/CKD_blank.zip. This one only has 21 sheets (not counting the General Journal). In each ledger account, cell E1 has the balance brought foreward( If any) from a previous accounting period; cell E2 contains a formula for keeping a running balance bewteen the debit column © and the credit column (D). What I’m trying to do is link the General Journal to the other sheets so that entries in the debit or credit column of the General Journal will appear in the C or D column of the proper ledger account.
Clark

I’ll have to figure out someplace to post a zip file of the sample workbook as the file is to big to attach to this post.
Clark

I worked on that post quite a while so as not to appear cranky, sorry if I failed. I appreciate the help I get on this forum very very much.
I’ll see if I can post the workbook. I understand that no matter how well I think I’m explaining it, it’s probably still pretty murky.
Clark

This is classic, accrual based accounting. And I disagree about Quickbooks. I already own Excel 97. In a few hours time I have figured out how to set up my ledger accounts using Excel. Quickbooks retails for $279.95. After I’ve spent that amount I still have to sit down and learn how to adapt my business to it. I hope I’m not giving the impression that I’m tearing my hair out over what I want to do with Excel. I’ve actually been quite successful so far. If I can’t go any further, that’s fine. I’m money ahead.
Clark

A form sounds like it would work. I have all the ledger accounts set up, all 37 of them, as worksheets in a single work book. Each has five columns: A for the date, B for a description of the transaction, C is the debit column, D the credit column, and D is the balance. Using a formula I found on this forum, I have the ledger accounts set up so that an amount entered in the C column of a debit account increases the balance, while an amount entered in the D column would reduce it (vice versa for the credit accounts). I could set up a form with the same 5 columns (or at least the first 4), but I need a way to get entries in the B, C, and D columns of each row in the form to go to the right worksheet. And furthermore, to be entered in the first available cell on the worksheet. I can select any number of worksheets and have amounts entered in the same cell of each of them, but I don’t want the amounts to go in the same cell of each sheet, but rather the first available cell of the same columns, because some sheets have more data in them than others. That way the balances are always correct. It seems like it would be a simple matter for someone who knows Excel well (which excludes me).
Clark

Thanks very much!

Isn’t the pivot table meant to analyze data that already exists in the worksheets? I’m looking for a way to enter data into the worksheets from Sheet 1(the General Journal).
Clark

Quickbooks seems like a lot more of a program than I need. My bookkeeping is very simple, with just a general journal and 20 ledger accounts. I’m just looking to avoid adding up columns of numbers.

Thanks, Klaus. That fixed it.
Clark

Klaus, would it possible to tweak this macro so that it will change the case of words that are all caps and possessives? When the script comes to a word like SMITH’S it changes it to SMITH’s.
Clark