Forum Discussion
Index and Match
Added the formulae that I believe to give the outcome you are after in Sheet4 (file attached). Note that I changed the Week-headers to plain numbers. In the shaded area, I kept the intermediate steps that I took in building the formulae in the weekly schedule above it. Just my way of going through such a task, step-by-step, in order to create a bigger formula. I'm sure others will have a different approach and can come up with a more elegant solution.
At the same time, I have added a pivot table as it seems to achieve a similar and more flexible) result without complicated formulae.
Added the formulae that I believe to give the outcome you are after in Sheet4 (file attached). Note that I changed the Week-headers to plain numbers. In the shaded area, I kept the intermediate steps that I took in building the formulae in the weekly schedule above it. Just my way of going through such a task, step-by-step, in order to create a bigger formula. I'm sure others will have a different approach and can come up with a more elegant solution.
At the same time, I have added a pivot table as it seems to achieve a similar and more flexible) result without complicated formulae.
Riny_van_Eekelenthank you for your reply, exactly what I was looking for.
I did think of a pivot but this data may be used for other things that a pivot would not be suitable for.
Thank you again for saving my sanity.
Machala