+ Reply to Thread
Results 1 to 11 of 11

Help with spreadsheet structure

  1. #1
    Registered User
    Join Date
    08-07-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    34

    Help with spreadsheet structure

    I am familiar with Excel. I need some help with the manner in which I should organize my spreadsheet.

    Currently, I am doing manual accounting. I maintain two books -- Bill (Sales) Book and Account Book.

    A sample bill from the Bill book looks like this:

    Date: 24/11/09

    Party Name: ABC
    Bill No: 123
    Items Qty Rate Amt
    .......................................
    .........................................
    .......................................
    Total Amt .........
    Add:Previous dues* .....
    Total dues .........

    *Previous dues must be the total of all dues receivable from the party.


    A sample page from the account book looks like this:

    Party Name:

    Bill Date Bill No. Bill Amt Amt Recd Total dues

    (The total dues column above is the total of all dues till date which is equal to "Total dues" in sample of Bill Book shown above)


    Notes:
    1. None of the payments are received bill-wise. So there is no need to cancel out a bill after payment is received.
    example, if bill 1 is of amount 13,400 and bill 2 is of amount 12,400, the party will never bother to look at either 13,400 or 12,400. The party will only look at the "Total dues" and pay some amount.

    How do I put the above manual system in Excel ?
    Last edited by iRounak; 11-24-2009 at 02:21 PM.

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Help with spreadsheet structure

    Here are a few places to start learning:

    Efficent Spreadsheet Design

    Excel/VBA Golden Rules

    Spreadsheet Design Tips
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  3. #3
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Help with spreadsheet structure

    ... or google around for templates for invoicing / bookkeeping

  4. #4
    Registered User
    Join Date
    08-07-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Help with spreadsheet structure

    Quote Originally Posted by teylyn View Post
    ... or google around for templates for invoicing / bookkeeping

    I am feeling a little lost now. I downloaded a template from here:
    http://www.freedownloadscenter.com/B..._Download.html

    This is what a page of my Bill book looks like.
    Basically, what is this template for?
    Is it only for taking a print-out of an invoice?
    Or can its content by linked to some other sheet in Excel so that my Account book can be created in the background while I am entering information in this template?

  5. #5
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Help with spreadsheet structure

    whoa, I'm not going to wade through that page ... can you be a bit more specific?

  6. #6
    Registered User
    Join Date
    08-07-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Help with spreadsheet structure

    Basically, i want to know if it is possible to dynamically update a cell in one sheet based on contents of another cell in a different sheet.

  7. #7
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Help with spreadsheet structure

    Yes.

    in a cell on one sheet, enter a = sign, then click the other sheet, click the cell and hit enter.

    Now the cell will display whatever is in the referenced cell.

    This is the simplest of cell references. Depending on what you want to achieve, there may be other solutions.

  8. #8
    Registered User
    Join Date
    08-07-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Help with spreadsheet structure

    Oops!! Sorry, I should have framed the question in a better way.
    Let us say i have "abc" in cell 1 and "213" in cell 2 in sheet 1.
    I want excel to look for "abc" in sheet 2 and enter "213" in the last column in the row containing "abc". If I change "213" to "214" in sheet 1, the change should also take place in sheet 2.

  9. #9
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Help with spreadsheet structure

    =VLOOKUP("abc",Sheet1!$A$1:$B$100,2,FALSE)

    or, if you define "abc" in cell A1 in Sheet2

    =VLOOKUP(A1,Sheet1!$A$1:$B$100,2,FALSE)

    adjust ranges to suit

    hth

  10. #10
    Registered User
    Join Date
    06-18-2009
    Location
    US
    MS-Off Ver
    Office 2003
    Posts
    23

    Re: Help with spreadsheet structure

    iRounak,

    Try this invoice template:

    http://spreadsheetzone.com/templateview.aspx?i=17

  11. #11
    Registered User
    Join Date
    08-07-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Help with spreadsheet structure

    Quote Originally Posted by suzzy View Post
    iRounak,

    Try this invoice template:

    http://spreadsheetzone.com/templateview.aspx?i=17
    I just dont want the invoice. I want accounting of the transaction in the invoice as well.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1