Output comes up with #VALUE! when I use OR() and SEARCH()

[owenmac99]

I want two worksheets where on Sheet2 the cell D19 is referencing cells from Sheet1 if they contain "RB". Here is the formula I put down but it doesn't work as it is only returning #VALUE!.

Please Login or Register  to view this content.

When I have it where it only searches specifically for "RB" but nothing else, it works fine:

Please Login or Register  to view this content.

. However this isn't what I want because there are some cells that contain RB along with other text.

I appreciate any with with the solution. Thanks!

[Phuocam]

You can use:

=IF(COUNT(SEARCH("RB", Sheet1!$B6), SEARCH("RB", Sheet1!$C6)), Sheet1!A6, " ")

Or:

=IF(COUNTIF( Sheet1!$B6:$C6,"*RB*"), Sheet1!A6, " ")

[owenmac99]

Perfect! That worked. Thank you! What do the *'s mean in the COUNTIF() statement?

Also, do you know if there is then a way I can get it so that the following entries then line up consecutively? Here is what happens currently:

Please Login or Register  to view this content.

It is copying from the table in Sheet1 but has all the spaces that don't qualify as empty. How could I do it so that they would go to the next row?
I would also like it to then add future entries automatically if that is possible.

[owenmac99]

I imagine that it could work something like I have to test if the cell = 0. If it does not equal 0 then test the next cell down. If it does equal 0 then put the text in there. However I'm not sure how to do that or if that is even correct.

[JeteMc]

If you could upload a desensitized copy of your workbook it might help someone provide a solution.
To upload a sample workbook (not a picture or pasted copy) click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.