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

No need for extra columns if you just add an extra condition for the date range.

Formula:

Please Login or Register  to view this content.

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.

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:

Please Login or Register  to view this content.

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.

Originally Posted by jimredfield

Dank u, dank u.

Graag gedaan!

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.

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.

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.

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.

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?