Using IF statements to create filtered RELATEDROW options

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.

1 Like

Picture 2

1 Like

Picture 3

2 Likes

Hi Martin_Estrada_Jr,

For Related-Row fields, now you can also specify a selector view with filter definition to limit which rows are made available as possible related-row field value options. Also, related-row options will be ordered as per the sort criteria of the view if sort is also defined on selector view.

More details here: Filter and Sort Related Row Selectors by View

Capture2

Capture3

4 Likes

That is an awesome addition. At the time when I made my sheet this wasn’t available. Good to know, thanks! Is there a way to have the filter change dynamically based on a value in another column? That way you do not have to set the filter for each task.

2 Likes

Is there a way to have the filter change dynamically based on a value in another column?

This is something I’ve requested as well, though in my case I wanted the filter to change based on a value of a cell located in the header … or in other words, I’d like to set a filter value to be equal to the value of a cell in the spreadsheet like A7.