Choose a random task after selecting a person and time available.

Interesting Problem.

I used Data Validation in B2 and C2 and an Array Formula in D2.

The formula is entered using Ctrl Shift Enter.

Formula:

Please Login or Register  to view this content.

The Formula looks Horrendous, but it isn't to bad if you break it down.

Formula:

Please Login or Register  to view this content.

Will return an array of the row numbers 4 to 11
Having enterd the formula in excel, select the ROW(A4:A11) in the formula bar and click f9 to see the array

Press ctr1 z to escape


alternatively enter =Sum(ROW(A4:A11)) using Ctrl Shift Enter which will return 60 which is 4+5+6+7+8+9+10+11.

Formula:

Please Login or Register  to view this content.

Converts the name in C2 into a number 1 to 4 which we can use in the offset function to select the column where the name is listed

Formula:

Please Login or Register  to view this content.

Selects the Name Column That Corresponds with C2, ie column B offset by 1, 2, 3 or 4

Formula:

Please Login or Register  to view this content.

Counts the number of times the Name Appears in the Names Column and where the Time in Column B matches B2

Formula:

Please Login or Register  to view this content.

Returns an array of 1s and 0s showing which cells in B4-B11 match B2

Formula:

Please Login or Register  to view this content.

Returns an array of 1s and 0s showing which cells in the name column match C2


multiplying the previous two formulas with

Formula:

Please Login or Register  to view this content.

will return an array of 0s and the Rows matching our name and our time.

So if we know how many matches we have ( Which Countifs gave us) we could use the RandBetween and the large functions to pick one of the non zero numbers in the array.

So now we have a row number we can use Index or Offset to select the right cell in column A, I used offest but

Formula:

Please Login or Register  to view this content.

would also have worked.