I found an interesting way to create filtered RELATEDROW lists for reducing selection list based on criteria. The purpose of this sheet was to create a task list that could be assigned to different people but I only wanted certain people available for certain tasks similar to a criteria system. First, I listed all of the people involved with the entire project. My tasks are broken into 6 categories called MCMs. I checked the box that each contact can be assigned to.
Next, I created 6 hidden sheets (one for each criteria), and wrote the following function, this being for cell A5;
=IFERROR(IF(OR(‘Contact Master List’!$E5,INDEX(‘Contact Master List’!$A5:J24,‘Contact Master List’!$E5,1),1),INDEX(‘Contact Master List’!$A5:J24,‘Contact Master List’!$E5,1)),’’)
This will show all options that fit the criteria for this sheet which is MCM 1. All other options that do not fit the criteria will return a blank space which is why this sheet looks odd. Notice which people fit the criteria in picture 1 and which people are showing in picture 2.
(Picture 2 In reply)
Now in a separate sheet I have a task list. For each task, I have an ASSIGN TO column which is a related row that looks at the sheet that matches the criteria wanted for the set of tasks. Since we are looking at MCM 1 tasks it will look at the MCM criteria sheet and only show those names.
(Picture 3 in reply)
What is nice is that the criteria lists dynamically change when you select different check boxes on the contact list. The downside to this method is that you have to set up each task to look at the correct criteria sheet so if you are constantly adding and removing tasks this will not be ideal. But if you have reoccurring tasks or a reoccurring amount of tasks this works very well. This isn’t a perfect method but it is better than having a huge list to look through when using related row.