Help duplicating information between Mutiple sheets with if statements

Occasional Visitor

Good day


I'm trying to set up sheet B were if the word "lost" is inserted in column F from sheet A then the values for column A in sheet A for that row is duplicated in sheet B column A, if the value for column F in sheet A is blank then no values are transferred. Can someone please assist with the formula for this? 


Thank you very much

2 Replies


FILTER can do this. Attached is a sample workbook with FILTER formulas.


=IF(ISNUMBER(SEARCH("lost",'Sheet A'!F1)),'Sheet A'!A1,"")

An alternative could be this formula in cell A1 in Sheet B.

Sheet A

Sheet A.JPG

Sheet B

Sheet B.JPG