Excel: Complicated lookup result


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.


For more resources for Microsoft Excel