Please Help!! Multiplying random cells from one spreadsheet and putting total into a cell on another

[Kiki_G]

Hi,

I'd be eternally grateful for any assistance with this. I'm relatively new to excel. I've been doing the organisation's accounts ie bank reconciliation etc on excel for the last year but it is incredibly time consuming.

We have funders - People and Places, Social Services, Garfield Weston etc who give us x amount of money. I firstly reconcile the bank statements and categorise each spend ( Room hire, van hire, food, phone, electricity etc see below) then I allocate the spend to a pot (ie People & Places or Tudor Trust etc) (Spreadsheet 1) I do a separate one for each month

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Cheque No[/TD]
[TD]Supplier[/TD]
[TD]Total[/TD]
[TD]Item[/TD]
[TD]Allocation[/TD]
[TD]Electricity[/TD]
[TD]Phone[/TD]
[TD]Satellite[/TD]
[TD]Rent[/TD]
[TD]Events[/TD]
[TD]Food[/TD]
[/TR]
[TR]
[TD]101248[/TD]
[TD]EDF Energy[/TD]
[TD]248.00[/TD]
[TD]Electricity[/TD]
[TD]Garfield Weston[/TD]
[TD]248.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]101856[/TD]
[TD]Coracle Hall[/TD]
[TD]35.00[/TD]
[TD]Room Hire[/TD]
[TD]Social Services[/TD]
[TD][/TD]
[TD][/TD]
[TD]35.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I then create a separate spreadsheet (spreadsheet 2)which shows how much was spent on each category from each pot so we know when the money is spent. ie finding all phone bills that have been allocated to Garfield Weston Since April) and adding them all up and putting the figure into spreadsheet 2.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Electricity[/TD]
[TD]Garfield Weston[/TD]
[TD]People & Places[/TD]
[TD]Social Services[/TD]
[/TR]
[TR]
[TD]Phone[/TD]
[TD]£total over all months[/TD]
[TD]£[/TD]
[TD]£[/TD]
[/TR]
[TR]
[TD]Satellite[/TD]
[TD]£[/TD]
[TD]£[/TD]
[TD]£[/TD]
[/TR]
[TR]
[TD]Food[/TD]
[TD]£[/TD]
[TD]£[/TD]
[TD]£[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This takes me a lot of time, as there may be 20 different cells containing an allocation to a pot of money. Is there any way to do it automatically? The main problem is that it is very random because I have to list each spend in the order they appear on the statement rather than being able to group them to each funding pot.

I'm so sorry if this doesn't make sense, I don't even know if it's possible and I don't seem to be able to upload the spreadsheets for you to see what I'm talking about.

Thank you so much for taking the time to even attempt to understand my waffle.

Kristie

 

[BrianMH]

As far as I can understand it sounds like a pivot table would do what you want. Are you at all familiar with them?

 

[Kiki_G]

I'm afraid I'm not familiar with pivot tables.

Is it terribly complicated? perhaps I can find out how to do it from somewhere?

Thank you so much for your help.

 

[BrianMH]

Introduction To Pivot Tables

 

[Kiki_G]

Thank you...that's my bedtime reading sorted.

You are a star!!