+ Reply to Thread
Results 1 to 21 of 21

Need help with a formula to populate a 'summary' table.

  1. #1
    Registered User
    Join Date
    10-10-2003
    Posts
    31

    Need help with a formula to populate a 'summary' table.

    I have a user-entry table, "MiscExpenses," with three columns - Code, Date, Amount.

    I have a second table, "MiscExpensesSummary," with the leftmost column called Code, and columns extending right from that labelled with a sequence of Monday dates (e.g 10/7/2013, 10/14/2013, 10/21/2013,...) that are 'week beginning on' dates. The Code column needs to be populated with every code in the MiscExpenses table, in alphabetical order, and the total amounts tagged with that code for each 'week beginning on' date populate the rest of the table.

    More details, and a set of sample tables are at http://www.griffinchaseoliver.com/li...celTables.xlsx.

    This is an in-house utility table and the formula does not need input-error trapping codes.

    Thanks for your help - this formula is over my head!

    Jim Redfield


    Moderators note: I have removed your email address, it is not good practice to posat sensitive/personal info in public forums
    Last edited by FDibbins; 10-16-2013 at 04:55 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Need help with a formula to populate a 'summary' table.

    Hi Jim

    Does the attached do what you require? (note the use of the helper column A)

    Regards
    Alastair
    Attached Files Attached Files

  3. #3
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Need help with a formula to populate a 'summary' table.

    No need for extra columns if you just add an extra condition for the date range.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Cheers!
    Tsjallie




    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,391

    Re: Need help with a formula to populate a 'summary' table.

    Jim, not all members can access file-hosting sites like that (company fire walls and stuff), so please upload your sample file to the forum

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Need help with a formula to populate a 'summary' table.

    Hi Tsjallie

    Thanks for the update - I shall remember that one.

    Regards
    Alastair

  6. #6
    Registered User
    Join Date
    10-10-2003
    Posts
    31

    Re: Need help with a formula to populate a 'summary' table.

    Alastair and Tsjallie - Thanks for the quick(!) solutions to my problem. Alastair, the sample table you populated with your solution works well, but I agree with you that Tsjallie's code is an improvement. ...except, Tsjallie, when I paste your code into the sheet Excel displays the dreaded 'formula you typed contains an error' message.' - Jim

  7. #7
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Need help with a formula to populate a 'summary' table.

    Sorry 'bout that. It's because I'm working with a Dutch version of Excel in which formula syntax is slightly different. So I need to translate 'm back from Dutch to English.
    But in doing that most of time I forget to change at least one of the semi-colons into commas. In this case I forgot 'm all
    Here the right syntax (I hope):
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    10-10-2003
    Posts
    31

    Re: Need help with a formula to populate a 'summary' table.

    Dank u, dank u. Will try the new code. - Jim

  9. #9
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,162

    Re: Need help with a formula to populate a 'summary' table.

    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  10. #10
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Need help with a formula to populate a 'summary' table.

    Quote Originally Posted by jimredfield View Post
    Dank u, dank u.
    Graag gedaan!

  11. #11
    Registered User
    Join Date
    10-10-2003
    Posts
    31

    Re: Need help with a formula to populate a 'summary' table.

    Tsjallie - Unfortunately, while the code works, what it does doesn't address the problem I'm wrestling with. I'll clarify tomorrow... - Jim

  12. #12
    Registered User
    Join Date
    10-10-2003
    Posts
    31

    Re: Need help with a formula to populate a 'summary' table.

    Tsjallie - I've added info to the Excel file I provided initially, changed its name to ExcelTables v02.xlsx, and attached it (I think!) to this post. If the greater functionality I'm asking for is too much to ask for, just let me know. Dank u. - Jim
    Attached Files Attached Files

  13. #13
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Need help with a formula to populate a 'summary' table.

    I think I get the picture. Already noticed that I overlooked one of your requirements about the code column needing to be populated.
    This will take some vba. I'm thinking of a user defined function. Is that a problem for you?

    And I assume that some values in your example should be shifted 1 column to the left? See marked values in the screenshot.

    By the way, are you attached to the tables. I mean can they just be ordinary cell matrices? These tables don't add much.
    Attached Images Attached Images
    Last edited by Tsjallie; 10-18-2013 at 04:14 PM.

  14. #14
    Registered User
    Join Date
    10-10-2003
    Posts
    31

    Re: Need help with a formula to populate a 'summary' table.

    No problem with VBA - I use it for simple stuff (would like to have the time to learn to use it more ambitiously).

    Tsjallie -

    Re "...about the code column needing to be populated...", that's true for the summary table. If there's no code associated with a user-entry, there's nothing to summarize). But the user can leave any of the columns in the user-entry table blank. Sometimes he may not have all the info needed to finish an entry - he knows he bought gas last Tuesday, but isn't sure what the amount was and will fill that in later. When he does, the formula for the summary table will pick it up.

    Yes, the column shift for "b" is needed. I screwed up the tab spacing there.

    I had spec'd a table for the user-entry matrix so the user could simply tab after each entry to create a new line for another entry below (which I think of as a table function). If it's a table it also makes it easy to sort the entries by code, date, or amount, an ability that seems worth having.

    The summary matrix can be anything you like as long as the cells can all be addressed dynamically (the right term?) as the matrix grows. I simply plan to look up the summary values and echo them in appropriate places on another sheet in the same workbook.

    Note that I will be manually editing the week-ending column header dates, dropping the older weeks and adding future weeks as we move into the future, and some codes will disappear from the summary matrix as a result. For example, if I have a code 'xm' for Christmas 2013 expenses incurred in November/December and then, in July, drop all week-beginning column heads earlier than February, the xm code line will disappear completely for lack of the November/December week-ending column heads into which they would go (even though the user entries for November/December are still in the user-entry table).

    Jim

  15. #15
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Need help with a formula to populate a 'summary' table.

    Hi Jim,
    here's a workbook what should do want you want it to do.
    I used the worksheet_change event to implement your requirements. This event can be found in the code modele of the worksheet.
    I have tried to keep both tables, but had the change the Expenses Summary table into a common range. Excel has a will of it's own which does not always comply with what's needed.
    For instance the formatting of column headers appeared to give a problem I have not (yet) been able to solve. Further more I'm just reluctant to hand over too much control to Excel.
    I compensated for that by automatically adding date columns if necessary though that was not a requirement. After all, you created this workbook to make your life more bearable.
    The Expenses Input table however is unchanged.

    The Event procedure is fully described in the modele itself.
    Just play with it. If you set a breakpoint at the beginning of the procedure you can step through the code line-by-line pressing F8 and see what each command does.

    There is (at least) one flaw:
    if you change an already entered code into a new (nonexisting) one that new code is put in a new row of the Expenses Summary, but the row with the old code will not be removed.
    If that's a problem it can be easily solved.
    Attached Files Attached Files
    Last edited by Tsjallie; 10-21-2013 at 04:35 PM.

  16. #16
    Registered User
    Join Date
    10-10-2003
    Posts
    31

    Re: Need help with a formula to populate a 'summary' table.

    Tsjallie -Wow! You put in a lot more work than I had thought my problem would need and I thank you for it. It will take me some time to understand the code and test drive it, so for now I'm going to mark this thread solved. I'll contact you again later if I get into trouble. Thanks again for your help. - Jim

  17. #17
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Need help with a formula to populate a 'summary' table.

    Hi Jim,
    it wasn't that much work. Documenting it was the larger portion ;-)
    If you have any questions, please let me know. If you have any trouble understanding the code, I'll explain line-by-line if necessary.

  18. #18
    Registered User
    Join Date
    10-10-2003
    Posts
    31

    Re: Need help with a formula to populate a 'summary' table.

    This thread may be closed, but I wanted to pass along what I learned about 'populating a summary table.' It turns out that a VERY simple pivot table in Excel, generated automatically(!) from a VERY simple list of entries (date | code | amount ), instantly solved the problem. Rather than beat up this forum with the details, I'd like to invite anyone who wants to know more to contact me. - Jim

  19. #19
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Need help with a formula to populate a 'summary' table.

    Hi Jim, I have been looking at a pivot table too, but couldn't get the columns right. Really drove me crazy!
    Could you post the workbook so I can see how you solved this.

  20. #20
    Registered User
    Join Date
    10-10-2003
    Posts
    31

    Re: Need help with a formula to populate a 'summary' table.

    forum.xlsx

    Here are the spreadsheets. One tab - out of pocket expenses - simply grows a list of dates, codes, and amounts; the other - pivot table - is the pivot table that digests the list. This works, but it has a few irritations.

    First you have to MANUALLY 'refresh' the pivot table after making new list entries. It's not automatic. Not Good.

    Second, I haven't found a way of copying values from the pivot table into a larger 'normal' table in the same workbook (the normal table has week-beginning column heads and row names that match the column and rows in the pivot table, so the 'intersections' match, but I can't seem to move the values at the pivot table intersection to the normal table intersection).

    There are other problems, I think, but those are the two that stand out for now.

    Jim

  21. #21
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Need help with a formula to populate a 'summary' table.

    First you have to MANUALLY 'refresh' the pivot table after making new list entries. It's not automatic. Not Good.
    You can set the option to refresh the pivot table on opening the workbook and that's it. But that's not what you want.
    In order to have the pivot table refresh whenever a new list entry is made, you need to use the worksheet_change() event
    and enter the following line there:
    Please Login or Register  to view this content.
    About the second problem: I'm not sure I understand what you wnat to achieve. Could explain further or - better - post an example of the result you want?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula for copying over relevant data from one table to populate another table
    By amasson in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-19-2013, 06:11 AM
  2. Replies: 2
    Last Post: 04-15-2013, 07:22 AM
  3. [SOLVED] Formula for copying over relevant data from one table to populate another table
    By amasson in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 02-25-2013, 12:41 PM
  4. Do I need a table array formula to populate names from a list?
    By MajorT in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-19-2013, 11:03 AM
  5. Creating a summary table using formula
    By arbrahul in forum Excel General
    Replies: 2
    Last Post: 05-29-2012, 12:15 PM

Tags for this Thread

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