Filtering Lists With Targeted Data in Multiple Columns

[ejschmitt2000]

I am a basketball official needing to make a list of scheded games and the
officials assigned to them. For any game we may use from two to four
assigned officials filling any one of the four named officials positions. I
have so far tried working with a spread sheet which includes four columns -
one each for each of the four positions. I am running into problems using
filtering because as soon as I filter the first officials column for a
certain offiials name, it disrupts me from filtering the 2nd, 3rd and 4th
officials columns for the same officials name.

How can I create and then filter (for eventual printing) a single spread
sheet where my goal is to list only those games for a specific person
regardless of the position they are assigned?

 

[Bernie Deitrick]

ej,

Use another column, with a cell at the top where you have a dropdown with all the allowable names -
you can create a list and reference that in Data Validation.

Then, below it, use a formula like

=NOT(ISERROR(MATCH(M$1,I2:L2,False)))

Where M1 is the cell with the list, and I2:L2 are the columns that may contain the name (for this
example, the formula would be entered into cell M2). Copy that cell down to match your list, and
then filter on that column, showing TRUE values to show all rows where the name entered in cell M1
appears.

HTH,
Bernie
MS Excel MVP