Use Multiple columns as filter

[erikmitchell]

I've been lurking in the forums, finding all kinds of neat things to do with a scheduling spreadsheet we use, so thank you all for questions and responses you've posted!

I have a question that I can't seem to find asked in a way that is relevant to me.. Caveat: I am using Excel 365 for Mac, so I don't have power query..

I schedule people in a list with 8 columns for how many possible people are needed (Leader 1, 2, 3, 4 are shown in attached image)

I need to use something like a pivot table with filter (see other image) to pull up the full schedule for "User x", regardless of which position they're in. So sometimes they're scheduled in column J, sometimes L, etc.

I can only get it to filter based on one column.. How can I filter for user 1 based on column J, K, L, or M?

There may be way better ways to do this, if I'm attempting with the wrong tools, please suggest that too!

Thank you for any tips!

[#2]

@erikmitchell You need to change your data entry table into a flat table where there is only one column for "leader type" and the values are "leader 1", "leader 2", etc. Then you need another column for the user name. If an event has three leaders, then that event will have three entries in the data entry table. 

THEN you can build a pivot table that is filtered by the User.

[#3]

@erikmitchell Great question.  Sorry to hear that you can't use Power Query, as this would have made it trivial!

I would set up an input cell that you can put the user selection in O1 or something, and set up column N to do a COUNTIF(J2:M2,$O$1), and filter on the '1's that should appear whenever the user is selected.  You could also remove the manual filtering step, by having a second column that counts the cumulative number of times that the '1' appears, and then use an INDEX(MATCH) to pick up the results.

Does that work for your question?

[#4]

@Ingeborg Hawighorst Thanks for responding 

Sorry, What do you mean make it a flat table?