I have a table with a list of items, and I want to create a new table with all the values that contains a certain value.

In the first list, are all the profucts that I usually buy and a cell with "YES or NO"

In the second list I want to put only the items with a "YES" on the first table.

See image below.

Thanks for your time.

enter image description here


I don't know how to remove empty rows from the second table, but I can make only the ones with YES show up.

Making it happen

First, select the first cell of your second table ("carrot", the headers don't matter), and type this in it:


You'll see it transform into a function. Then click the YES/NO column of the same row to add it to the table. Click on the dropdown in the new addition to the formula box, and check "Preserve Column".

Fig. 1

This will allow you to copy and paste over the entire row without worrying about the referenced column changing. Add = "YES" to the end. You should now have something like this:

IF(<Table 1::$B2> = "YES"

Next, add a comma, and click the corresponding cell in your first table:

IF(<Table 1::$B2> = "YES",<Table 1::A2>

Do not preserve the column on this one. You want it to show the corresponding cell in the table. Add ,""), like so:

IF(<Table 1::$B2> = "YES",<Table 1::A2>,"")

Hit the big green checkmark, and copy and paste the cell to fill the second table (or just as many items as you want to show), and you're done!

So what does all this gobbledygook mean?

The IF command has three parts:

IF(condition, if-true, if-false)

If the condition is true, it shows the if-true portion, otherwise it shows the if-false portion. So it shows the value of the other cell if the deciding cell is "YES", or blank ("") if not.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .