Hi Erik, tks for your reply
The boxes are located in another sheet, it´s for a tool of management to control the status of some activities of a project.
It´s like this (inside autoshapes) because we use it to index in the presentation for the team project.
The calculations are like this:
1. In sheet2 we have the forecast for beginning and end of an activity (column F and G respectively, column B is the name of the activities).
2. In column C we calculate how many days have been passed until that day since the beginning of the activity.
--> =Networkdays(F1;Today())
3. In column D we calculate the total planned duration of the activity
--> =Networkdays(F1;G1)
4. Then, in column E, we divide these results to get the status (%) that activity should be
--> =C1/D1
5. In column H is the real status, this information is estimated and comes from the project team.
Get it?
With this percentages (columns E and H) i have to fill the boxes in sheet1. They are located vertically. This sheet is like this:
Column B: The same name activities of sheet2
Column D: There is a shape in each cell with the results (%) in column E from sheet2
Column E, F, G,...: Real status (%) of the activities by week, also in in shapes, they are filled with information stored in column H in sheet2.
Column C: the difference between column B and actual status (in that week).
The shapes and the background of the cell have some differente colors depending on the status, but this is no problem to do manually.
I hope you understood!!
TKS