Hi All,
I currently have a spreadsheet with the following sheets which are tables, I also have a worksheet for every month and creating a monthly view of each day in their associated worksheet, I basically need a VBA macro to use the below information to construct a matrix such as below depending on the month listed, in each monthly worksheet I will also have a field which identifies the current year, this will also need to be used because the user will be allowed to choose the year to display from a drop-down list. In the matrix the days will need to be coloured depending on status. G = Green, B = Red, W = White (White = hasn't completed yet), also is it possible for the macro to enter the reason as a comment for a day marked as 'B'?
Hope I've explained it clearly, feel free to ask if I haven't, thanks in advance
- Server_Listing
<tbody>
</tbody>
- Exceptions
<tbody>
</tbody>
- Worksheet August (Matrix View) Cell B8 = 2013 [Button Run VBA]
<tbody>
</tbody>
Attached a pic for matrix:
I currently have a spreadsheet with the following sheets which are tables, I also have a worksheet for every month and creating a monthly view of each day in their associated worksheet, I basically need a VBA macro to use the below information to construct a matrix such as below depending on the month listed, in each monthly worksheet I will also have a field which identifies the current year, this will also need to be used because the user will be allowed to choose the year to display from a drop-down list. In the matrix the days will need to be coloured depending on status. G = Green, B = Red, W = White (White = hasn't completed yet), also is it possible for the macro to enter the reason as a comment for a day marked as 'B'?
Hope I've explained it clearly, feel free to ask if I haven't, thanks in advance
- Server_Listing
| A | B | C | D | E | F |
| Server Name | Client:Customer Name | Server Type | Location | IP Address | Comments |
| ServerAB | Customer 1 | ArcServe | QLD | Test | |
| ServerXY | Customer 2 | ArcServe | NSW | Test | |
| Server02 | Customer 3 | ArcServe | NSW | Test | |
| Server03 | Customer 3 | ArcServe | NSW | Test |
<tbody>
</tbody>
- Exceptions
| A | B | C | D | E | F | G | H | I | J | K | L | M |
| Title | Customer | Date | Client Server | Error Code | Sequence | Reference | Reason | Current Status | SLA Exempt | Day_Calc | Month_Calc | Year_Calc |
| Customer 3 - Server03 | Customer 3 | 8/08/2013 | Server03 | Test | Test | Test | Test Reason | Good | TRUE | 8 | August | 2013 |
| Customer 2 - ServerXY | Customer 2 | 9/08/2013 | ServerXY | Test | Test | Test | Test Reason | Bad | FALSE | 9 | August | 2013 |
| Customer 1 - ServerAB | Customer 1 | 16/08/2013 | ServerAB | Test | Test | Test | Test Reason | Bad | TRUE | 16 | August | 2013 |
| Customer 3 - Server02 | Customer 3 | 9/08/2013 | Server02 | Test | Test | Test | Test Reason | Bad | TRUE | 9 | August | 2013 |
<tbody>
</tbody>
- Worksheet August (Matrix View) Cell B8 = 2013 [Button Run VBA]
| Server | Server Type | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | |
| Customer 1 (merged cells) | ServerAB | ArcServ | G | G | G | G | G | G | G | G | G | G | G | G | G | G | G | B | W | W | W | W | W | W | W | W | W | W | W | W | W | W | W |
| Customer 2 (merged cells) | ServerXY | ArcServ | G | G | G | G | G | G | G | G | B | W | W | W | W | W | W | W | W | W | W | W | W | W | W | W | W | W | W | W | W | W | W |
| Customer 3 (merged cells) | Server02 | ArcServ | G | G | G | G | G | G | G | G | B | G | G | G | G | G | G | G | |||||||||||||||
| Customer 3 (merged cells) | Server03 | ArcServ |
<tbody>
</tbody>
Attached a pic for matrix:
Last edited:
