I am looking for a Macro that will make the process in the document as simple as possible. Its currently a lot of manual work and I would like to automate the process.
The document involves a two tables in a Matrix form input Volumes and Budget and an out Actuals. In the same document we have Process, Unit and Team. This shows the split in the process. In the row section we have a Row – S. This is the sum row and this is the first row under each new process or Unit.
The task is to get the actual numbers based on the formula A/B*C. In this formula we divide an element in the Volumes table (A) with another base element (B) in the volumes table and multiply it by the same corresponding element in the budgeted section.
I need a macro that will repeat this formula along the row until it finished the months and then move on to the next row and do the same. Every time though taking into account the (S) row as the baseline row for referencing the formula. Only other challenge is that the macro should be flexible enough because the sub-processes under each Unit say S1 are not equal to those under S2. Also of note is that the columns in the table Volumes will not always be 15 cells, it may reduce or increase. Thus the macro should be very dynamic.
Anyone with an Idea on how to do this.?
Here is a sample file - https://www.box.com/files#/files/0/f/0/1/f_2514961751
The document involves a two tables in a Matrix form input Volumes and Budget and an out Actuals. In the same document we have Process, Unit and Team. This shows the split in the process. In the row section we have a Row – S. This is the sum row and this is the first row under each new process or Unit.
The task is to get the actual numbers based on the formula A/B*C. In this formula we divide an element in the Volumes table (A) with another base element (B) in the volumes table and multiply it by the same corresponding element in the budgeted section.
I need a macro that will repeat this formula along the row until it finished the months and then move on to the next row and do the same. Every time though taking into account the (S) row as the baseline row for referencing the formula. Only other challenge is that the macro should be flexible enough because the sub-processes under each Unit say S1 are not equal to those under S2. Also of note is that the columns in the table Volumes will not always be 15 cells, it may reduce or increase. Thus the macro should be very dynamic.
Anyone with an Idea on how to do this.?
Here is a sample file - https://www.box.com/files#/files/0/f/0/1/f_2514961751
