Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Still working on reproducing d (Excel 97 SR 2)

    Still working on reproducing double-entry bookkeeping using Excel. I've got the ledger accounts set up as 37 sheets, each with a unique name, such as "Cash" and "Accounts Receivable." The first sheet is the General Journal, which uses columns A through E. I'm trying to write a formula so that if the word "Cash" is entered in B of the General Journal, an amount entered in D will appear in the first empty cell under C in the "Cash" sheet. If the word "Cash" is entered in C of the General Journal, an amount entered in E will appear in the first empty cell under D in the "Cash" sheet. See, columns D and E are the debit and credit columns respectively in the General Journal. The debit and credit columns of the ledger accounts are C and D. Actually, the formula has to be written so that if any of the 36 ledger account names is entered in B or C of the General Journal, the corresponding entry in D or E will be appear in the first available C or D cell of the named ledger account, depending on whether it is a debit account or a credit account.
    Clark

  2. #2
    New Lounger
    Join Date
    Dec 2000
    Location
    Indiana
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Still working on reproducing d (Excel 97 SR 2)

    Good luck. Excel is a great general purpose application so it might prove adequate.

    Personally, I'll stick with QuickBooks.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Still working on reproducing d (Excel 97 SR 2)

    Have you thought about using a pivot table on Sheet 1.
    You will then be able to filter/customise for any ledger, showing detail or totals etc.

    This will save a lot of coding and test effort and will require litte ongoing maintenance if a new ledger has to be created.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Still working on reproducing d (Excel 97 SR 2)

    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

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Still working on reproducing d (Excel 97 SR 2)

    Sorry, I thought that you were trying to automatically create a ledger view of the data in the general journal.
    Pivot tables will provide these views for you.

    As far as entering the data goes, I can only suggest direct entry into the spreadsheet or the use of a form based on the spreadsheet as the alterntaives to extensive coding. note however atha the data validation features may be able to prevent errors such as incorrect ledger categories, entry of DR and CR amounts into the same transaction, or entry of the same cheque number twice etc whithout resorting to VBA.

    I hope this helps.

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Still working on reproducing d (Excel 97 SR 2)

    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

  7. #7
    Star Lounger
    Join Date
    Apr 2001
    Location
    Winchester, Hampshire, United Kingdom
    Posts
    64
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Still working on reproducing d (Excel 97 SR 2)

    I have achieved this in the past, but my advice, unless you have a serious reason to do this, is to use a bespoke accounting program, like Quickbooks or MYOB (which I prefer). It can be done using Excel, but it's hard work and requires a huge amount of "housekeeping". To make it work properly you will need to use array formulae, and even if if you know Excel well, it is a mountainous task for little return, and prone to lots of problems. You will need to build in a great deal of error-checking to prevent incorrect data. Basically, an accounts program is a classic database application rather than a spreadsheet application, and wityh low-cost accounting programs now available, that would be a better option.

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Still working on reproducing d (Excel 97 SR 2)

    You'll have to use array formulae and/or VBA to automate the 37 ledger postings, which isn't going to be easy to set up nor to maintain, and correcting prior entry errors may pose a challenge. However, the thoughts on pivot tables make me wonder:

    Is this essentially a cash set of books, or is it accrual? If cash, your cash ledger becomes your G/L, you code every entry and use the pivot tables as "ledgers", or more appropriately think of them as account-specific views of the G/L. The use 37 pivot tables on separate sheets act as your specific ledger data. (You'll have to stop thinking of your subsidiary ledgers as ledgers, and start thinking of them as views on the G/L data).

    I sincerely think you'll save more time and money setting up QuickBooks (or MYOB, which I don't know) than setting up Excel.

    (It must be a mere 25 years since I've worked on a manual set of books!)
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Still working on reproducing d (Excel 97 SR 2)

    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

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Still working on reproducing d (Excel 97 SR 2)

    Your profile doesn't say where you are from, and I was "pushing" QB on the basis that in the US street prices for QB Basic start around US$130, so I apologize if the economics don't work for you. Also, I was coming only from my own perspective, and the Lounge is still here to help, please don't be put off by my arguments.
    <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>

    So, please continue to ask questions. If you could post the WB (with censored or fake data) it might help give interested Loungers a basis for working on the original question you asked. (Otherwise we have to emulate your model to work on your problems, and that can be time-consuming.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Still working on reproducing d (Excel 97 SR 2)

    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

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Still working on reproducing d (Excel 97 SR 2)

    With everybody including me yelling at you to use QB, I think you have the right to be a little cranky. Let's see if we can help with what you are trying to do.
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Still working on reproducing d (Excel 97 SR 2)

    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

  14. #14
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Still working on reproducing d (Excel 97 SR 2)

    <P ID="edit" class=small>Edited by gwhitfield on 18-Jan-02 06:45.</P>Hyperlinks added

    OK, the Excel file with my workbook in it is at <A target="_blank" HREF=http://soli.inav.net/~ckenyon/CKD_blank.zip>http://soli.inav.net/~ckenyon/CKD_blank.zip</A>. 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

Posting Permissions

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