Using a formula to create a daily summary of a monthly rota

[maxf2k]

Hi all

I was hoping you might be able to tell me where I'm going wrong!!


I intend to create a daily allocation sheet based on timings of shifts -I intend to select the date from drop down box and then data is going to be pulled from a monthly rota.

This should make it easier to work out who is working on any given day, without time-intensive searching of the rota.

I have tried to create an array formula based on some of the advice that I've seen on this forum - however the data I'm getting out is incorrect.

I've attached the example spreadsheet that I'm working with that has the formula in situ.

There seems to be a consistent error that it gets the first two allocations correct and then the second two allocations incorrect.

I've tried to troubleshoot it, but I can't see where I've got it wrong. Any help would be much appreciated.

[Glenn Kennedy]

Here's a non-array formula to do what you want:

=IFERROR(INDEX($1:$1,AGGREGATE(15,6,COLUMN($C$1:$AH$1)/(($A$3:$A$33=$B$39)*($C$3:$AH$33=C$39)),ROWS(B39:$B$39))),"")

If you want to continue to use an array, for some reason, use this:

IFERROR(INDEX($1:$1

at the start.

[AliGW]

Welcome to the forum.

WHERE in that cacophony of colour are we meant to be looking, please? Define 'correct' and 'incorrect' - what are you getting and what are you expecting? And what's the formula?

[CARACALLA]

C40=IFERROR(INDEX($C$1:$AH$1,AGGREGATE(15,6,COLUMN($C$1:$AH$1)-COLUMN($C$1)+1/($A$3:$A$33=$B$39)/($C$3:$AH$33=C$39),ROWS($C$40:C40))),"")

Copy across and down