Consolidating data

[Handyann]

Hi all,

I'm a raw newbie, both here and using Excel, so I hope you'll bear with me if I ask something that may have been asked before. I'm not really sure what to enter to search for an answer, as my problem is a bit complex.

Basically, I've just taken over the accounts for a local Events group and I need to streamline them and produce a condensed overview. The individual monthly worksheets have sections for income and expenditure, which I need to keep as they are in some format, and the individual entries are detailed - eg a donation of X pounds from person Y; expenditure of X pounds on posters; expenditure of X pounds on staff etc.

Each monthly sheet may have more than one entry which can be allocated to a specific category - e.g 3 donations in; 2 payments out for publicity;4 payments out for stationery etc.

I intend to allocate fixed broad categories to slot each entry into - eg donations; publicity; staff; event income etc. - to consolidate them and would ideally like to be able to have Excel automatically add up the entries in a category and export them to an annual overview sheet, so we can see at a glance what's coming in from where and what we are spending out on.

My questions are - can this be set up to be done in one automatic operation and how could I achieve it please? I've read about macros and suspect that's what I need, but I have no knowledge (yet) of the appropriate coding. If I understand it correctly, I think I need to have a column on each worksheet to take the category names and then to insert these appropriately next to each entry. Then I have to tell Excel what to do with them, which is where I'm stuck.

Any help you folks could give would be much appreciated
Many thanks
Ann

[Bernie Deitrick]

Ann,

Do you have one workbook, with many sheets, or many workbooks with one sheet each, or many with many?

Could you post a sanitized/shortened workbook - with what you have, and a sheet showing what you want?

[Handyann]

Hi Bernie and thank you so much for replying.

There is just one workbook per year with 12 monthly sheets in it. I want to add an overview sheet which brings together the data from the monthly sheets in a consolidated form.

I can't post the original workbook because it contains sensitive information, but I'm just now making a mock one to practice on and can post that in a few minutes (if I can figure out how!). It should give you an idea what I need to do.

Thank you
Ann

[hoyasaxa215]

Here's a template I use for consolidated data on a summary worksheet. Without being able to see how your data are organized, maybe this template will be of some help to you. Feel free to post any questions.

[Handyann]

Many thanks for posting the template hoyasaxa215. I've attached a sample sheet, following the format I've inherited from the previous person, although he didn't know anything about Excel other than using autosum and I think the layout might not be that great. I haven't filled in Feb and March, but they would follow the same format. Anyway, as you see, there are income and expenditure sections, each with detailed entries which have been allocated broad categories.

I need Excel to add the monthly amounts for each item in a category and then copy the category and the category total to the summary sheet - for instance Mr Smith and Jane Jones made donations of £30 and £50 respectively, both these are in the Donations category which needs copying to the summary sheet and totaling, to finally show Category - Donations £80 and likewise for the other categories. I hope that makes sense.

The overall aim is to have each months' entries condensed, totaled for each category and then exported to the summary sheet.

Thank you
Ann

[Handyann]

your template is almost what I need hoyasaxa215, but I notice that the worksheets and the summary sheet are identical in layout, where mine wouldn't be. I need Excel to find all the instances of eg catering purchases on a given sheet, compile them together and copy the result to the summary page. Am I asking for something that isn't possible? It seems a lot to ask!!

[Bernie Deitrick]

Try this macro - the resulting sheet should become the source for a pivot table.

Please Login or Register  to view this content.

[Handyann]

I've mocked up the summary sheet, which just has the January entries on it. Ideally, as monthly sheets are added to the workbook, the categories and totals would automatically be added to the summary, if this is possible. I could do it all manually if need be, but I thought there might be a way to avoid all that work :-)

[Handyann]

Thank you Bernie. Your reply came in as my last post went! I'll try it and get back to you.

[Bernie Deitrick]

This version will create the PT for you... and also changes expenditures to negative values for easier summation.

Please Login or Register  to view this content.

[Handyann]

Many thanks again Bernie - you're most kind. I'll give it a try.

[Handyann]

Hi Bernie,

Sorry for the delay in replying.

I think I may have done something wrong, as the last macro shows an error when applied to the worksheets. The debug button shows this section highlighted -

'ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
Worksheets("Summary").Range("A1").CurrentRegion.Address(False, False, xlR1C1, True), _
Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="'Pivot Table'!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14'

and the error is - 'Runtime error '5':
Invalid procedure call or argument.

The macro does create a blank Pivot Table sheet and a summary sheet with the income and expenditure in the same column (which is I guess why you applied the '-' to the expenditure figures). I don't need as many columns in the summary sheet actually, thank you - just the Category (donations, catering etc, etc) and the totaled amount, in a pair of columns each for income and expenditure .

Two questions - is it actually possible for Excel to automatically use the layout as in the January sheet... income on the left and expenditure on the right, rather than in one column? Only because it's the format previously used and the one the auditor prefers...

...and is it possible to program Excel to pick out the entries for each Category from the monthly sheet and add them together before listing them on the Summary sheet? E.g, to take all the Donation entries and add their amounts together before adding this total to the Donations row on the Summary, and performing the same process for each set of Category entries? Ideally, as each new monthly sheet is completed, the Category totals should automatically be added to the Category totals on the Summary sheet - or is this too ambitious of me?

I wonder if what I'm trying to do isn't possible for the program to carry out automatically? I don't know what limits it has.

I really appreciate your help. If you don't have time to fiddle with this, perhaps you could just add notes to the macro code so that I know which bits do what and then I can play with it myself. I learned HTML and CSS in much the same way!

Thanks again
Ann

[Bernie Deitrick]

Ann,

Ooops! You are using 2007, so change the 14s to 12s - just use this to overwrite that line

Please Login or Register  to view this content.

The rest of you questions are addressed by the pivot table - once you get the code to work, let me know if you need changes.

[Handyann]

Hi Bernie,

Thank you very much - the code does now work and produces a pivot table and a summary sheet.

I can see that I can extract data from the PT by selecting criteria from the drop-down boxes, but is there a way to automatically add items together and send that total and its relevant category to the Summary sheet please?

I suppose I'm thinking of instructions something similar to 'search the sheet, pick out everything with the category X, add it's numeric values together and copy the category and total to the summary sheet. Do this for all categories'. Easy to say, but I don't have a clue how to ask Excel to do it. Is there a way to use the PT to send info to the summary please?

Oh, by the way, I did have a play around with a copy of the macro coding earlier and managed to get the summary sheet to have the right headings in the right places, I just don't know how to send the rest of the info to them.

I did say I was a complete novice at this, lol!!

Many thanks for your time and trouble.
Ann

PS. Oh, wait a minute - I've been messing about with the PT settings and have found I can isolate the categories and totals! Presumably there is a way to then use that to inform the Summary? Not quite automatic, but close!! I'll go fiddle some more!

[Handyann]

Hi again Bernie,

Well, I've got the PT to isolate the categories and total them as I wanted, thank you!

The only things I now need to do (if possible) are

- to get the category list on the PT report out of alphabetical order and into a format which presents the income first and the expenses following, so they are grouped together and not scattered about over the list,

- to get those values ported over to the summary sheet and set that up to include each sheets' totals as they are created or, if that isn't possible, at least to get the PT printout to a bigger size - it's about 5cm square at the moment! Not very impressive for the paper accounts file, lol!

I'm lots of steps nearer to what I wanted to achieve - thanks to you Bernie. I don't know if the rest is possible or not, but I'll try and find out.

Many thanks again.

[Handyann]

Hi Bernie,

I hope you're well and still here!!

I've been using the code you kindly gave above with limited success - having to tweak some things manually that I can't sort out - but now I've upgraded to Excel 2010 and, despite changing the 12's back to 14's (I hope that's right?), the summary sheet and thence the pivot table won't update from the worksheets at all. If I add a new worksheet, change details on or delete an existing sheet which appears on the summary, nothing changes on either summary sheet or pivot table.

BTW, reading through the original posts, I realise I had the order of sourcing wrong - the summary informs the PT, not the other way round.
I've looked everywhere on the net to find a solution, but with no luck, so I've come bothering you again.
I would be so grateful if you could please sort me out!!

Many thanks
Ann