Hi,
I am hoping someone can assist me in improving my formula (I think I need an array formula but am stuck on writing it).
In the attachment I have a list of staff who request to move from their city to a choice of another 2 cities. My objective is increase my staff requirement by 3 in BNE (cell F1). I have sorted the list of staff that want to move to another city by "Date Rec'vd". A couple of staff have a preference 1 and a preference 2. Once I have satisfied getting 3 staff to move to BNE then I stop calculating. My formula is doing this now.
My problem is that staff 200036 didn't get their preference 1 (because the system stopped calculating once I got 3 staff (net) to go to BNE so I want to grant their preference 2 which is higher up in the list because of the date rec'vd sort order.
If a staff has a preference 1 request with a date earlier than their preference 2, my current formula works, but in the case of preference 2 before preference 1 it doesn't work.
Hoping someone can assist.
Regards
GB
I am hoping someone can assist me in improving my formula (I think I need an array formula but am stuck on writing it).
In the attachment I have a list of staff who request to move from their city to a choice of another 2 cities. My objective is increase my staff requirement by 3 in BNE (cell F1). I have sorted the list of staff that want to move to another city by "Date Rec'vd". A couple of staff have a preference 1 and a preference 2. Once I have satisfied getting 3 staff to move to BNE then I stop calculating. My formula is doing this now.
My problem is that staff 200036 didn't get their preference 1 (because the system stopped calculating once I got 3 staff (net) to go to BNE so I want to grant their preference 2 which is higher up in the list because of the date rec'vd sort order.
If a staff has a preference 1 request with a date earlier than their preference 2, my current formula works, but in the case of preference 2 before preference 1 it doesn't work.
Hoping someone can assist.
Regards
GB