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? [IMG]http://www.danrich.co.uk/images/excel_example.png[/IMG] Any help you can give would be greatly appreciated.
This question generated 21 answers. To proceed to the answers, click here.
This thread is current as of April 14, 2014.