Summing expenses based on month & Vlookup

Hi Guys,

I'm having a bit of an issue.

I want to be able to sum expenses for the year-to-date ie. If I want the sum for the expense item to March, I want to be able to type in March and it sums up the expense up until March.

The other issue is that I want to combine a "vlookup" type entry into this formula. I want it to look for the specific expense and then sum up the expense up until any desired month.

The reason for this is that the expense is situated on different rows in the different worksheets.

Is this at all possible - I hope I've explained myself correctly...

Please help.

Thanks

Thanks oldchippy,

I'm still having an issue.
For ease of explanation, I've attached a file with some data.

You'll see that I have no problem when collecting the data from each company for the month of march (simple vlookup)

The problem is in the Year to Date(YTD) calculation.
With only 3 companies to manage, I could use the sum,offset and match but this is assuming I know exactly where the expense is situated in each row. You may think I'm being lazy here with only 3 companies, but the reality is that are more than 140, and that is a bit much.

I want the formula to locate the expense in any row in column A, and then sum the monthly expense up until the month selected from the drop-down list.
Am I asking too much?
I should then be able to edit the formula to change just the sheet that I need the data from.

Please help...
Oh, and thanks in advance....

No Probs,

Here it is...

Hello antonc,

The attached workbook contains 2 the User Defined Functions YTD and YTD_All. The code for these macros appears below. YTD takes 3 arguments: Expense to search for, the eding month as a number, and the Name of the Worksheet to search.
YTD_All takes only 2 arguments: The Expense, and the ending month as a number.

Example

Please Login or Register  to view this content.

UDF Macro Code

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Often we enter what is really data into format that is a semi report format.

If we treat data as data you can then use a pivottable, they are very powerful and really easy to use.

See attached

Thanks for the replies.

The YTD_All works perfectly, but I'm not comfortable with macros (ie don't have a clue how to adapt them to my situation). I'll work with what you've given me and go through it step-by-step.

The pivot table is also useful, but that involves the manipulation of the data into a structure. I'm guessing that would be too time consuming.

Thanks again guys - appreciate it.

I'd be investing the time to set up the data so you can use pivottables.