Complex Scheduling in Excel

Are you still using Excel 2010?

OK - please update your forum profile without delay. Thanks.

We only need three formulas, if I'm not mistaken.

In order to fill the first 2 columns in trainee scheduling:

Formula:

Please Login or Register  to view this content.

(not sure formula translator is up to the task with arrays, but it should work on the file)

For the remaining columns

Formula:

Please Login or Register  to view this content.

And finally, for the presenter scheduling

Formula:

Please Login or Register  to view this content.

HTH,

Francesco

In the first formula, we use FILTER() on the TRAINEES table to get only the rows that match the location. The location can be found with the last 4 letters in cell E10 on the Schedule sheet. This, however, returns all the columns, so we apply again FILTER(), using an array {1,1,0} to get only the first two columns. This formula will spill down and on the right.

For columns G to P, the informations are in the table ROLES, we only need to match the Role and the Training Topic, then TRANSPOSE() the array returned by INDEX(). This formula will spill to the right, and must be dragged down.

For the last table, a regular MATCH() looking for Location and Topics on the Presenters table is enough.

Btw, I just realized I missed one request for marking the presenters, here is the formula for G9, then drag to the right

Formula:

Please Login or Register  to view this content.

then you can copy/paste to rows 17 and 25.

Thx for the feedback and added rep.

HTH,

Francesco