Index IF formula for data in Row bases on Column data

Marielitos

Essentially I’m trying to move rows and its data from a master sheet to a separate sheet based on the yes/no answered on a cell within the row. I keep getting #unparseable. Possibly make it automatic transfer.

I am unable to use the Automation workflow due to formulas and cross references etc.

thank you

Melissa Yamada

Hello @Marielitos can you share your formula as well as some screenshot?

Marielitos

The information is in a source sheet, I have a column with YES/NO, the only rows i want to to get copy are the rows with YES in that column.

The destination sheet is the same but I need the rows and its info to get copied automatically if the column contain the YES.

INDEX/MATCH formula with an IF statement to conditionally return the data: =IF([Column with "Yes/No" value]@row = "Yes", INDEX([Sheet Name]::[Range of data to copy], MATCH(@row, [Sheet Name]::[Column to match against], 0), 1), ""). 

Im not sure if I need to do a continuous formula to transfer information just from some columns or is it easier to just do the entire row.

Thank you

Melissa Yamada

Hello @Marielitos

If you're going through the path of using formulas, you'll need to create the formula on each column on your destination sheet. Also, I think you'd want to use INDEX/COLLECT functions instead of the INDEX/MATCH since you have the criteria of Yes/No.

So something like this for every column:

=INDEX(COLLECT({Column X},{Unique Identifier},[Unique Identifier]@row,{YES/NO Column},"Yes"),1)

Another easier way is just to do the automation and copy the row when the Yes/No column is equal to "Yes". Formulas and cross sheet references won't be copied over to your destination sheet only the values/ data will be copied over.