Populate calendars based on office location

Welcome to the forim.

This will be a whole lot easier if you are using MS365, and as you have SEQUENCE in the workbook, you cannot be using Excel 2016 - please update your profile so that we know what we are working with.

Thanks.

If you don't mid to insert some field into the table.

Please try, insert 2 field with

D
=[@Office]&[@Date]

E
=IFERROR(LOOKUP(2,1/(D$1:D1=D2),E$1:E1),"")&","&B2

In the result sheet

A6
=IFERROR(MID(INDEX(Table1[[#All],[NameList]],AGGREGATE(14,6,ROW(Table1[[#All],[NameList]])/(1/(Table1[[#All],[DateOffice]]="AP"&DATE(YEAR($A$3),MONTH($A$3),A5))),1)),2,1000),"")

Regards.

Note : Please chang your profile, Excel 2016 didn't have function SEQUENCE.

With MS365, you could use this in A5:

=FILTER(Table1[Staff],(Table1[Office]="AP")*(Table1[Date]=DATEVALUE($A$3)+A5-1),"")

I have changed your conditional formatting rule to an all-in-one formula:

=AND(NOT(ISTEXT(A5)),OR(A5>DAY(EOMONTH($A$3,0)),A5<1))

Clearly if you want to combine W and AP, then you are going to need to make much more space on the calendar.

For AP and W together, it would be:

=FILTER(Table1[Staff],(Table1[Office]<>"WFH")*(Table1[Date]=DATEVALUE($A$3)+A5-1),"")

Any quetions?

Here's the calendar set up for AP and W combined.

I have tried to copy what you did on the AP page over to the WFH page:
=FILTER(Table1[Staff],(Table1[Office]="WFH")*(Table1[Date]=DATEVALUE($A$3)+A5-1),"") )

I'm not sure why but it isn't working the way that the AP sheet does I've reuploaded with what I tried for the WFH office

You had not applied the conditional formatting - I've fixed that for you. See my previous post.

I have changed your conditional formatting rule to an all-in-one formula:

=AND(NOT(ISTEXT(A5)),OR(A5>DAY(EOMONTH($A$3,0)),A5<1))

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Have we got there now? Or are you using pivot tables instead?

Great - thanks for letting us know.