Filter function

[Hattie]

Hello,

I adopted the code (Thanks to Fluff) from my sports team spreadsheet to a report I am doing at work.

I was originally just using a fixed table, which worked but was not as elegant.

I make a nice pretty report with colours etc. (on a different sheet) based on the data in a pivot table.

the report sheet contains the formula:

FILTER(FILTER('Staff FTPR'!A14:'Staff FTPR'!L60,'Staff FTPR'!A14:'Staff FTPR'!A60='Staff FTPR'!C8),

COUNTIF('Staff FTPR'!A13:'Staff FTPR'!L13,'Staff FTPR'!A13:'Staff FTPR'!L13))

it works great - most of the time...

But, there's always a but

now 'Staff FTPR'!C8 (end of the first line) points to a pivot table drop-down box.

now if I select "shift 1" in the drop-down box then the REPORT table is filled with staff on "shift 1"

if i select "shift 2" in the drop-down box then the table is filled with staff on "shift 2"

great - just what I wanted.

but if I select "shift 1" and "shift 2" in the pivot table dropdown box the dropdown box will now contain the text "(Multiple Items)"

OK...

I understand this

but...

now the filter function is not returning any people from either shift as staff are marked as "shift 1" or "shift 2" and not as "(Multiple Items)"

so this all makes perfect sense.

so how can i resolve this?

I have tried in the above filter command using an IF statement with a wildcard (*) but wildcards don't work in if statements as far as I am aware.
and an OR statement, no luck so far...


one-way I was considering how to fix this (not sure if it will work until I try it) is in the staff table that populates the DATA TABLE with what shift each person is assigned to
is to have an IF statement that changes the staff member’s assignment from "shift 1" or "shift 2" to "(Multiple Items)"

but this seems a bit of a kludge

i was hoping to start my learning using good code and not fudging it.

any thoughts as a better way to proceed would be appreciated.

thanks