Not sure if an array is the best way to deal with this problem, but as I'm not too hot on arrays I hope somebody can help.
I have a list of data that holds a Cost Centre in column A, Activity Code in column B and monthly costs in columns C-N in a worksheet called 'Data'
In a seperate worksheet called 'Summaries' I have a list of Cost Centres and a Summary type an adjacent cell.
I have a worksheet for each Cost Centre that lists that cost centres activity codes and gives a total per month.
There are 5 Summary types that can be linked to any of the 18 different Cost centres, so 1 Summary type can be made up of 2 or more Cost Centres.
I also have a worksheet that needs the totals of each Activity Code in a Cost Centre for each summary type, ie if Cost Centres 15121, 17128 & 17129 are flagged with Summary type AED I need the AED worksheet to SUM all Activity codes in the 3 Cost Centre worksheets.
I need some script that will add a SUMIF formula to a list of Activity codes in the AED worksheet but as I don't know which sheets it will need to be linking to until my macro has looked at the list of Cost Centres and Summary types I'm not sure if using '=SUMIF(rest of furmula)+SUMIF(rest of formula)+SUMIF(rest of fumula)+, etc, etc is the best policy.
I would post some screen shots as the above looks complicated to follow, but not sure how to do this. If someone can let me know I will post a reply with the required screen shots.
Thnaks in advance
I have a list of data that holds a Cost Centre in column A, Activity Code in column B and monthly costs in columns C-N in a worksheet called 'Data'
In a seperate worksheet called 'Summaries' I have a list of Cost Centres and a Summary type an adjacent cell.
I have a worksheet for each Cost Centre that lists that cost centres activity codes and gives a total per month.
There are 5 Summary types that can be linked to any of the 18 different Cost centres, so 1 Summary type can be made up of 2 or more Cost Centres.
I also have a worksheet that needs the totals of each Activity Code in a Cost Centre for each summary type, ie if Cost Centres 15121, 17128 & 17129 are flagged with Summary type AED I need the AED worksheet to SUM all Activity codes in the 3 Cost Centre worksheets.
I need some script that will add a SUMIF formula to a list of Activity codes in the AED worksheet but as I don't know which sheets it will need to be linking to until my macro has looked at the list of Cost Centres and Summary types I'm not sure if using '=SUMIF(rest of furmula)+SUMIF(rest of formula)+SUMIF(rest of fumula)+, etc, etc is the best policy.
I would post some screen shots as the above looks complicated to follow, but not sure how to do this. If someone can let me know I will post a reply with the required screen shots.
Thnaks in advance