Filter function

Hattie

New Member
Joined
Oct 7, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Watch MrExcel Video

Forum statistics

Threads
1,126,992
Messages
5,622,027
Members
415,874
Latest member
JockPC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top