allocate costs into matrix based on week number

Dear Forum,


As I am fiddeling around now for quite some time I can't help myself to find a solution, I hoped that someone can support me out or give me a hint how to approach my problem.

i am looking for an approach to automatically fill in costs into a cash flow table based on the type of occurance and summarized per week number.

The table itself is a bit more comlplex as it contains also various external sources like MYSQL data and pivot data, therefore the structure of the table can changed.

On the x axis I do have the years and week number:

Year: 2015, 2015, 2015, 2015,...,2016, 2016,...etc,
Week: 31, 32, 33, 34, ...., 1, 2, ...

On the y Axis I have hundreds of costs like:
Costs, Amount, each x day of, period,
rent, €300, 5, monthly
car pool, €50, 45, quarterly
insurance, €2000, 30th Nov, yearly
etc,


I may refer also the attached excel sheet here. costs allocation into week matrix.xlsx

Now my general question - does someone have an idea how to automise this sheet, so that costs in the yellow section are allocated into the matrix of weeks and costs according to the correct week number they do occur in?
So far I was not able to come up with a smart formular first of all to identify the correct week in which the cost would occure and secondly to prove this against the x axis in order to allocate to costs into the matrix.

Can someone give me a hint how to this could work in terms of formulars ?

Any help or hints are highly appreciated.

Thanks in advance

Arie

normally, you dont build the matrix, you enter data as:

year,week, item, cost
2015,31, rent, 300
2015,32, car pool, 45

then run a pivot table and IT will build your matrix.

Originally Posted by ranman256

normally, you dont build the matrix, you enter data as:

year,week, item, cost
2015,31, rent, 300
2015,32, car pool, 4
then run a pivot table and IT will build your matrix.

Regrettay i cannot change the format and create a pivot.