formula to look for cells with values and output result to different column

[Red fuji]

I need help with a formula that would look for cells in column 'A' that contain a value. Then it would read the corresponding status in column 'B' and copy the value in that cell to a different sheet. If the cell contains a value but the corresponding status cell is blank then the result would be "NA". Cells containing no values can be ignored.

Thanks for your time and effort.

[ben_hensel]

I think this is what you want.

Please Login or Register  to view this content.

A2 in the status tab checks if A2 in sheet1 is blank:
TRUE: Check if B2 in sheet1 is blank:
>>TRUE: Pass whatever is in B2 of sheet1
>>FALSE: Pass text "NA"
FALSE: Make A2 in the status tab blank

[Red fuji]

ben_hensel.
Thanks for the formula.
but I failed to mention that I dont want to transfer over the empty rows; the copied result would not have empty rows in between them.
how do i revised the formula to do that?
i updated the attached file.

= IF(sheet1!A2 <> "",IF(sheet1!B2 <> "", B2, "NA"),"")

Thanks.