LinkBack URL
About LinkBacks
I need to search a range of 2 cells (they would be next to each other as formulas on one sheet OR not next to each other as raw data on another sheet.) for a specific text string. I would like the output to display the text string I am searching for if it is found, and if it is not found, display a blank cell. On the calcs sheet, in columns E-K, I am attempting to search for the text string that is in the header. I found something, but it is obviously not working correctly. These formulas will be entered in columns E - J (pink) on the calcs sheet. I am attaching a small sample workbook.
Obviously all teams listed for SL and CF SL should be accounted for. Our users are supposed to make sure their SL is included in the CF SL, but they don't always. Look at item #13 (highlighted in yellow) The SL is Team 1, but the CF Service Lines are Team 2, 3 & 5. So on the calcs sheet each of those 4 teams should have their team name listed in their own column as they are listed in one of the 2 columns that is part of the range. For any records that have a blank in the CF SL Column (D), then only the SL shown in Column C should display in the coordinating column... Record #14 should display Team 1 in Column E, and Columns F-J should be blank for that record.
Search either calcs sheet - Columns C & D
OR data sheet - Columns B & C
I tried many possibilities, but nothing works, and I just don't understand why. I went back to a previous file when I needed to do something similar, and that range was much larger, and it worked. But now that my range is small, it won't work. I don't get it, you would think it would be easier. The formula I use on my large previous ranges was:
=IF(OR(SUMPRODUCT(--ISNUMBER(SEARCH(error,$A2:$P2))),SUMPRODUCT(--ISNUMBER(SEARCH(error,$R2:$AI2)))),"Human Error","")
Register To Reply
Bookmarks