Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    better way than pivot tables (Excel 2000 9.0.3821 SR-1)

    Hi Excel gurus,

    I am not a big user of excel, and so I am not sure of the best way to approach my problem.

    My sister and I have bought a house with 3 flats. She uses excel at work and has created the accounts spreadsheet. We currently have all the accounts spread acorss several sheets, with details for each flat, joint accounts, payments to each other etc. When we come to work out total figures, it usually involves a lot of cut and pasting, and differing final values... Its a bit of a mess to be honest.

    I wanted to set up one sheet which would have **all** the payments made by myelf or my sister. Each payment would have a code for flat number, joint account or shared, and a code for who paid it. I would then want to have 3 or 4 dynamic sheets which come from this master data. One for each flat. one for the joint account, one that compares payments made by my sister, and by myself. (so we can see who had paid most etc)

    Now, this to me is a project for a database, and I think I might have to write a bespoke app to do this. However my sister uses excel, and is happy with that, so my question is...

    Is there a way to do this in excel, I have looked at pivot tables, and they dont seem flexible enough for what I want.

    Ideally I would love examples of similiar spreadsheets out there on the web. (with tutorials!)

    Or maybe someone could tell me that excel is the wrong app for this task, and then I will stop looking.

    Thanks,






    David.

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: better way than pivot tables (Excel 2000 9.0.3821 SR-1)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> David

    IMHO:

    <<< Is there a way to do this in excel, I have looked at pivot tables, and they dont seem flexible enough for what I want. >>>

    Yes. Not a true pivot table project, but something like that will work.

    You should look at formulas to:

    SUM for adding things.
    VLOOKUP to look up values from other worksheets, like the code you give yourself and your sister.

    Many may come in play but for now, this is what I came up with.

    <<< Ideally I would love examples of similiar spreadsheets out there on the web. (with tutorials!) >>>

    Sure a lot of code is passed around here, but you need to give us some examples. Make a copy of your worksheets and remove any sensitive data and post it for us to see.

    <<< Or maybe someone could tell me that excel is the wrong app for this task, and then I will stop looking. >>>

    No to me this sounds like an accounting endeavor, and spreadsheets are made for such accounting needs. Excel is very powerful, but you need to know how to use it just as you use any tool in your toolbox.

    Post more detailed examples as to what you need, and what is your vision, and I am sure you will be happy with the replies.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    New Lounger
    Join Date
    Jan 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: better way than pivot tables (Excel 2000 9.0.3

    Hi Wasim,

    I have enclosed a sample of what I am trying to do.

    I am hoping to only enter data onto the first worksheet, and that all other worksheets will be updated dynamically. Also I am hoping that if I add an extra line to the first worksheet with another expenditure; that magically, all the other linked spreadsheets will update - see I don't want much do I?

    Any help is greatly appreciated, even if it is just the area of the manual in excel I should start reading.

    Thanks for you time/ help in this matter.


    david.

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: better way than pivot tables (Excel 2000 9.0.3

    If you really are comfortable with coming up with a bespoke database for this kind of problem, then you probably have the right instinct for the situation. Naturally, if that database is designed in Access, it would capitalise on your sister's comfort level with Excel. What you are suggesting can be done with Excel using a large number of links and/or conditional LookUp tables. I am not aware, however, of a worked example you could copy/cannibalise. HTH
    Gre

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: better way than pivot tables (Excel 2000 9.0.3

    If you use a pivot table you can get "almost the results you are after.
    Set the PAGE to "category" and instead of all the extra sheets you can get the "category summaries" in 1 sheet.
    Here is an example of the output.
    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center>F</td><td align=center valign=bottom>1</td><td valign=bottom>Category</td><td valign=bottom>Flat A</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>2</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=center valign=bottom>3</td><td valign=bottom>Sum of Cost</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td valign=bottom>Paid By</td><td align=center valign=bottom>4</td><td valign=bottom>ID</td><td valign=bottom>Date</td><td valign=bottom>Description</td><td valign=bottom>Room</td><td valign=bottom>Receit ID</td><td valign=bottom>David</td><td align=center valign=bottom>5</td><td align=right valign=bottom>1</td><td align=right valign=bottom>01/01/2004</td><td valign=bottom>Cooker</td><td valign=bottom>Kitchen</td><td align=right valign=bottom>1</td><td align=right valign=bottom>400</td><td align=center valign=bottom>6</td><td align=right valign=bottom>8</td><td valign=bottom>(blank)</td><td valign=bottom>plumbing in sink</td><td valign=bottom>Kitchen</td><td valign=bottom>(blank)</td><td align=right valign=bottom>150</td><td align=center valign=bottom>7</td><td align=right valign=bottom>9</td><td align=right valign=bottom>01/12/2004</td><td valign=bottom>Radiators</td><td valign=bottom>(blank)</td><td align=right valign=bottom></td><td align=right valign=bottom>300</td><td align=center valign=bottom>8</td><td align=right valign=bottom>10</td><td valign=bottom>(blank)</td><td valign=bottom>electrician wire in cooker </td><td valign=bottom>(blank)</td><td align=right valign=bottom></td><td align=right valign=bottom>75</td><td align=center valign=bottom>9</td><td valign=bottom>Grand Total</td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom></td><td align=right valign=bottom>925</td></table>

    If it must be in the format you could create a "function" that takes the category and extracts the info on the data to the active sheet. You could have it called whenever you activate a sheet.

    THis would be more involved than just using the Pivot Table
    Steve

Posting Permissions

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