Fill data in chart from array

Question

I am trying to make a sheet that fills in for people who will be absent for the current week.

The dates 1/8/24 thru 1/12/24 auto generate based on day of the current week.

I would like the names to fill in automatically when A, B, P, E $ F# are in a cell for that date. (Letters are reason codes

Has anyone got an easy way to do this I currently just type them in each week.

Answer 1

Yes - the FILTER formula automatically spills to the cells below if there are multiple entries for the same day:

Answer 2

Thanks. In B15:

=FILTER($A$3:$A$10, INDEX($B$3:$Z$10, 0, MATCH(B14, $B$2:$Z$2, 0))<>"", "")

Fill to the right to F15.

This works great.

Can this work if there are multiple letters on the same day.

example if on the 8th Chris had an F2.

Answer 3

Thanks. In B15:

=FILTER($A$3:$A$10, INDEX($B$3:$Z$10, 0, MATCH(B14, $B$2:$Z$2, 0))<>"", "")

Fill to the right to F15.

Answer 4

They are dates that are formatted to show only the day number (format custom d)

example 1/1/24 is 1 and 1/2/24 is 2.

Answer 5

What do B2:Z2 contain? Numbers 1, 2, ... or dates 1/1/2024, 1/2/2024, ... formatted as d to display only the day number?