Situation:
I am trying to create an array which is reasonable complex as follows:
I have a calendar which is setup with the staff listed across the columns
For each of the staff there are several rows below to enter their competencies which from a list of more than a dozen and each staff can have different competencies
Under the above is the calendar where each day is a row
For each day we enter if the staff are working days, night, extra days, extra nights or it is blank when they are not in.
What I want:
What I want to do is calculate for each day is calculate for each competency the number of staff who are in on days (including extra days) and do the same for nights (including extra nights) so that we can identify deficiencies in competencies i.e. manning.
Issues:
I tried this using excel arrays, but excel works through rows then columns and doesn’t provide the result I desire. I want it to work down rows then along the columns.
Is there a way of doing this with arrays?
Temporary solution:
I have created a formula to get about this; however, it is a very large formula calculates through the rows for each column and has the major set back that it doesn’t allow staff to be added or moved. It also increases the file size by a lot which is causing an issue on SharePoint.
The problem with the temporary solution is that the numbers of staff are increasing and each formula competency required 4000 characters. I have competencies 18 on each row * 365 days, this is then repeated for three years and will only increase. This is excluding the overtime (extra days or extra nights) which will double the formulas again!!!
Getting an array to work would be a major reduction in this and make my life so much easier!!!
If you know of a solution let me know. I am trying to refrain from VBA at the moment, if there is no other solution then this will have to be the go but I’m concerned about the processing demand to go calculate the code for the 18 cells *365 for each change.
Jon
Bookmarks