***Need help with formula

[hercule_p2001]

Hello!

I have a sheet on which in the range AL3:AS14 there is data retrieved from Access database. Row AL3 is the header row with e.g. following names:

Month - Cat1Expense - Cat2Expense - Cat3Expense - - - - Cat7Expense

The column Month has the entries for month in the yyyymm format from July to June:
200906
200907
200908
...
201006

In the same sheet from cell A1 I have the expense report made which currently I fill manually from the range mentioned above. The data is transposed with month as the header and category expenses in rows. BUT I can't use the transpose function because there are some fixed expenses that do not change and so are interspersed with the changing expenses like following:
Note: FE is for Fixed Expense. CE is for Category Expense

*** Jun-09 Jul-09 Aug-09 & so on
FE1 500 500 500
FE2 120 120 120
CE1 :get the data from Cat1Expense for the respective month:
CE2 : ---------same for Cat2Expense-----------------------:
Subtotal: SUM of the above(FE1+FE2+CE1+CE2)

CE3
FE3
FE4
Subtotal (CE3+FE3+FE4)

CE4
CE5
CE6
CE7
Subtotal(CE4 to CE7)

TOTAL - sum of all subtotals.

Can we use array formula is some way to make the task easier?


Thanks

 

[westconn1]

i guess you could transpose the cat expenses, then insert rows for the fixed

 

[hercule_p2001]

Thanks for responding but there is a problem with that. I have to do this at the end of each month. So if I did it for this month and inserted the rows for fixed expenses, how will it then work the next month? What I am trying to achieve is to automate the whole process since the data is already there. I am looking for some nested formulae into the cells that should get the data.