mercredi 29 août 2018

Return value of cell if adjacent cell contains specific text?

I have 2 columns in Sheet 1 (named Projects), "Projects" in column A and "Status" in column B. The number of rows is dynamic.

In Sheet 2, I want to extract the projects that meet a certain string criteria and populate a table. For example, if the Status is "Operating", take the project name in column A for that row and put it in a table.

I tried =IF(Projects!B1:B="Operating",Projects!A1:A,"") but it returns the names of the projects in the same row position as in Sheet 1. So if there is a 2 row gap in Sheet 1 between two operating projects, it keeps the 2 row gap in Sheet 2.

How do I get rid of the gaps?

Aucun commentaire:

Enregistrer un commentaire