Good evening all, I've used this forum many times as a guest and discovered numerous results for problematic formula I am working with, however, this one has finally got me completely stuck so i'm hoping an excel guru out there may be able to help me. I am assuming I would need to use VBA to achieve this and although I am fairly experienced in using VB.Net to write standalone applications I have never used it in Excel before so not too sure how alike they are.
I have added an image of a small part of my spreadsheet and will attempt to explain what I am trying to achieve.
Firstly, I have a column of roles C8:C20. The following columns show availability of staff, a blank cell shows them as available, a cell with a code means they are not available for whatever reason. There are a total of 13 staff, in column D at 00:00hrs no one is booked off therefore the total available is 13 as circled. In column J we have four staff booked off leaving a total of 9 staff available as circled in the entire column.
What I am trying to achieve is to find out what roles are available in that hour. For example. Under row J I would want the following totals if possible:
OIC = 2 (We have 3 when fully staffed but 1 is booked off)
DVR = 2 (None are booked off)
FF = 5 (We have 7 when fully staffed but 2 are booked off)
NQ = 0 (We have 1 when fully staffed but that one is booked off)
2 + 2 + 5 gives me the total of 9 in the heading of column J but I wanted to break that down to show me what the individual totals are below each column.. Is this at all possible?
Any help you can give would be greatly appreciated.
I have added an image of a small part of my spreadsheet and will attempt to explain what I am trying to achieve.
Firstly, I have a column of roles C8:C20. The following columns show availability of staff, a blank cell shows them as available, a cell with a code means they are not available for whatever reason. There are a total of 13 staff, in column D at 00:00hrs no one is booked off therefore the total available is 13 as circled. In column J we have four staff booked off leaving a total of 9 staff available as circled in the entire column.
What I am trying to achieve is to find out what roles are available in that hour. For example. Under row J I would want the following totals if possible:
OIC = 2 (We have 3 when fully staffed but 1 is booked off)
DVR = 2 (None are booked off)
FF = 5 (We have 7 when fully staffed but 2 are booked off)
NQ = 0 (We have 1 when fully staffed but that one is booked off)
2 + 2 + 5 gives me the total of 9 in the heading of column J but I wanted to break that down to show me what the individual totals are below each column.. Is this at all possible?
Any help you can give would be greatly appreciated.