Excel Help / Advice

[ukgimp]

I know excel can do a shit load of stuff, I know it can do what I want but I dont know how difficult or time consuming it is. Any chance someone could give me their thoughts on what I want it to do please.

It consists of three tabs:

Data:
Which has the following headers:

Payee   Amount   Date   Method   Accounting Code   House   Notes

I get someone to add the amounts and the accounting code is dependant on what the expense or income was made for.

Account Codes
Which is a list of different income and expenditure account codes, which do not change and are really used a reference for the two other sheets

So in my case 09 = cleaning costs

Summary
This is the complicated tab bringing in the data from the two other sheets. In a nut shell it adds up each cost code entry from the data sheet and picks the right month for the item and puts the correct figure in that month column.

If anyone can help I can put a link to the file so people can see what I am on about.

If anyone can point me in the right direction, or even tell me what the functions I am suggesting might be called that would be a great help.

Cheers

Rich

[ukgimp]

Upload of file

[grnidone]

Dang!  I have the wrong excel to do this...it's easy as can be...I could get this done in like 15 minutes..can you save it in the .xls file format?

[ukgimp]

I cant turn down that offer Heather :-)

Just watching that Tony Schwartz video at the moment :-)

[grnidone]

ACk...this isn't what I thought you wanted...I think this can be done if you put the sheet in a different format though..
Are you your messenger?

[grnidone]

Grin.    I'm not sure how to do this, but I know it involves an "IF" STatement, and I've always wanted a reason to learn how to do this, so I"ll hammer away on it for a bit..

[grnidone]

OK...digging into this..it isn't that difficult at all BUT this sheet needs to be put into a different format to make this work...I've added you to my MSN chat..but you're nowhere to be found..ping me when you can.

Is this a dump from an accounting program?

I know this sounds geeky, but I love doing little crap like this..

[grnidone]

OK.  I've found out how to do this.  BUt my excel wont do it as it is not new enough...

But I get the logic, so I'll explain it to you.  Do these steps:

1.  In the summary page, you need to put the month names along the top..April, may, june etc starting in D11 and going across.  That's the only way to do this by month

2.  Change the name of Account Codes to AccountCodes (no space)

Now...this formula should do ya right for the month of May in D12. 

=SUMIFS(Data!B2:B8,C2:C8=MONTH(5),E2:E8=AccountCodes!A1)

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2,
         criteria2], ...)

The SUMIFS is how you add things when you have to make two comparisons.
Notice you're adding up the values in the Data sheet (Data!) from B2 to B8 IF the values in C2 to C8 are in the month of may (5) AND if the cells from e2 to e8 have the value equal to the value on the AccountCodes sheet in cell A1.

If you want this for June, you need to change the Month to 6 for the cell in the summary sheet E1.

That should do it just fine.  Let me know how it works.

http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx

[ukgimp]

Hello Heather.

Thanks very much for this. I will have a look.

I am having trouble logging into MSN, , plus i dont get onto there very much.

I will let you know how it goes.

Thanks

Rich

[ergophobe]

I cant turn down that offer Heather :-)

Just watching that Tony Schwartz video at the moment :-)

:-)

BTW - I coupled that with WorkRave running on my computer and found it pretty helpful. Of course, during my rangering summer, much of my day is programmed out weeks in advance (I can tell you right now what I'll be doing pretty much every minute of July 30 between 12:30 and 22:00).

[Adam C]

if I've interpreted this correctly, you could do the following (though very hacky - and there may be a cleaner way):

1. on Data tab in column H add a header called "Costs"
2. in cel H2 add something like the following:

=VLOOKUP(E2,'Account Codes'!A$1:B$23,2,FALSE)

and copy the formula down the column

3.  build a pivot table off the Data tab (Date in columns, Costs in row, Sum of Amount in Values)


Not perfect but would go some way towards the view you're after if you're still struggling

[ukgimp]

Got it working. You dont need to link accountcodes.

You need to make  a year and month column in Data

=MONTH(C12)

=YEAR(C12)

which give just a numeral in colunm D and E

You create a year a month row in Summary

Then you create the sumif as Heather suggested

=-SUMIFS(Data!$B$2:$B$5000,Data!$G$2:$G$5000,$A15,Data!$D$2:$D$5000,D$3,Data!$E$2:$E$5000,D$1)

[Leona]

Hi guys

Sorry just seen this far to late, if you ever have excel issues pm/email me as James is an excel whiz, thats what he does, so will most likely be able to point you in the right direction and save you a few hours on google.