Hi Guys,
I was wondering if someone could help me with two issues that have been driving me nuts for the past two months. I have attached the spreadsheet to make the problem easier to understand.
Many MANY thanks in advance guys
1. Calculating the trend by colour
How it all works
* Looks at the colour trend for the last three weeks and returns: Amber or Red if the project was mostly Amber/Red for the last three weeks, BUT Green if the project was green at ANY time during the last three weeks.
* Please see the column below, which takes the data from the last three weeks (boxed in BLUE for visability)
* Next update will see Column Ms data being added onto the table. This will change the Last 3 wk trend to : Green, Red, Red, Green.
* For unsubmitted data, the cell should be blank (e.g, cell M10) - right now my macro returns a zero
Basic Requirement
Calculate the trend colour for the last three weeks
The table should always look at the last three columns during its calculation
(please see table attached for the example)
2. Project cost summary by last value for each month
How it works
* Forcasts takes the value for the last date of every month for each project
* When the table is updated with column H, it will replace the values for April (Apr)
Basic Requirement
Input the value for the last date of every month for each project from the 'Data Sheet'
(please see table attached for the example
I was wondering if someone could help me with two issues that have been driving me nuts for the past two months. I have attached the spreadsheet to make the problem easier to understand.
Many MANY thanks in advance guys
1. Calculating the trend by colour
How it all works
* Looks at the colour trend for the last three weeks and returns: Amber or Red if the project was mostly Amber/Red for the last three weeks, BUT Green if the project was green at ANY time during the last three weeks.
* Please see the column below, which takes the data from the last three weeks (boxed in BLUE for visability)
* Next update will see Column Ms data being added onto the table. This will change the Last 3 wk trend to : Green, Red, Red, Green.
* For unsubmitted data, the cell should be blank (e.g, cell M10) - right now my macro returns a zero
Basic Requirement
Calculate the trend colour for the last three weeks
The table should always look at the last three columns during its calculation
(please see table attached for the example)
2. Project cost summary by last value for each month
How it works
* Forcasts takes the value for the last date of every month for each project
* When the table is updated with column H, it will replace the values for April (Apr)
Basic Requirement
Input the value for the last date of every month for each project from the 'Data Sheet'
(please see table attached for the example