How to Filter a Report based on items found in ONLY multiple sheets

RiseUpPNW

I've had two Pro Desk support appts that have not resulted in a resolution, and it was suggested I ask here. Hopefully I can be very clear about what the issue is and I apologize if I'm long winded.

My non profit runs a group of 200+ volunteers that fundraise at varying venues and over 200+ events per year. Each venue has specific guidelines and trainings for working that venue.

As such, we have a "Master Member List" that has each member and what trainings/requirements they have and if they are current or expired.

Meanwhile, each event has it's OWN event sheet that is specific to only the people volunteering at that particular event. What we need to do: Compare the individual event volunteer list to the master member list to identify if each person that is signed up for that particular event has the needed requirements to actually volunteer there.

I currently have a report that I have set up that pulls from both the master member list, and the event sheets I am currently looking at, along with the requirement columns I need to see. I have it grouped by Volunteer so that it groups the sign up with the master member list. This works "ok", in that I can scroll through to see who is signed up at those particular events. The PROBLEM is that I have to scroll through the report of 200+people to pick out the 20 that might be signed up.

The request: Is there a way to FILTER the GROUPS in the report so that the report ONLY shows me the results from the member list for those that are on the event sign ups. I don't want to have to scroll through 180 volunteer master member list items when they are not even on any of the sign ups I'm auditing. I hope this makes sense!!! No matter how I filter the report, it always gets rid of the items I'm trying to compare. Here are screenshots to hopefully make it clear.

This is the master list that tracks which volunteer has which requirements current.

And this is the current report that pulls from both the master list and the two events I'm currently looking at (events will be constantly changing, so I need a solution that allows to me change the report in include new events each week) You can see that I really only need lines: 18/19, and 24/25/26 as those are the only two people that are working the events. So I can see that they both have the items needed to work at the events I'm pulling. I do NOT want the report to include MASTER MEMBER LIST lines for Jenkins, West, Colvin or Vasquez as they are not signed up for the events I'm auditing. .

Thank you in advance! The only solution I've found is to filter the report by volunteer and selecting each and every one individually in the filter and this is time consuming and prone to mistakes and missing people and negates the ease of the report in the first place)

I attempted some sort of MATCH/INDEX formula that could be on my event templates to compare to the master sheet in a helper column but was unable to successfully make this work and am not positive that's even what I needed as that formula is above my knowledge level.

KPH

I would be very tempted to do this with an INDEX MATCH formula in the event sheet and not a report. I have confidence in you :)

Let's do this….

It looks a lot because I have written out every step but you can do this.

Add a date-format column to your event sheet. I call it LU TPS Exp Date (LU for Look Up to differentiate it from the column in the master sheet - which is unnecessary but might make it easier to follow the instructions)

In that column type the start of an INDEX formula:

=INDEX(

Then click on the pop up option to reference another sheet.

Select your master member list from the tree on the left. (see 1 on image below)

Then highlight the column with the TPS Exp Dates in. (see 2 on image below)

And enter a sheet reference name. I called it Master Member List TPS Exp. (see 3 on image below)

Your formula will now look like this:

=INDEX({Master Member List TPS Exp Date})

The name you set yup has been added, along with the closing parenthesis.

This says you want to return a value from the range you just set up.

So now, all you need to do it tell it which row.

Before the closing parenthesis, type

,MATCH(Volunteer@row,

So your formula now looks like this

=INDEX({Master Member List TPS Exp Date},MATCH(Volunteer@row,)

This says the row will be the one where the value in the Volunteer row on the current matches something.

Then set up a cross sheet reference to identify the "something", by clicking on the pop up option to reference another sheet, just like you did before.

This time, highlight the column with your volunteer names in.

And enter a sheet reference name. I called it Master Member List Volunteer.

Now click insert reference.

Your formula will now look like this:

=INDEX({Master Member List TPS Exp Date}, MATCH(Volunteer@row, {Master Member List Volunteer}))

Enter another comma and a 0 to make the match exact (to be on the safe side).

=INDEX({Master Member List TPS Exp Date}, MATCH(Volunteer@row, {Master Member List Volunteer}, 0))

Click enter.

The date from the Master Member list will appear.

Add another date-formatted column for the Food Prep Exp Date.

Copy the formula you have just made (not the date, but the formula) and paste it into a cell in the new column.

Highlight the TPS Exp Date cross sheet reference, including the { and }

Click on Reference Another Sheet.

This time highlight the Food Prep Exp Date column and give that a name. Click Insert Reference.

The formula will update with the new reference.

=INDEX({Master Member List Food Prep}, MATCH(Volunteer@row, {Master Member List Volunteer}, 0))

The rest stays the same as you are still matching the Volunteer in this sheet with the Member List Volunteer column.

You can make both of these into column formula and lock the column.

Now you have the data you need in the sheet and will only see the dates for the volunteers that you add to that sheet.

Good luck!