multiple sheet IFERROR formula - help required

[JAdams1983]

Hi,

I have a spreadsheet with multiple sheets within. (Sheet A, Sheet B, Sheet C etc).

For each row in sheet C, I want it to search Sheets A and B for cells containing parts of text, then show the text from that cell in the corresponding cell. The formula I have currently is:

=IFERROR(IF(FIND("SW", SHEETA!$B$2:$AL$8), SHEETA!$B$2:$AL$8, ), )

This works for Sheet A but if there are two cells within that sheet showing "SW" in their cells, it's only showing the first cell.
Also, how do I add sheet B to this also so it's searching the full document?

Any help MUCH appreciated!

Thanks!

 

[Rijnsent]

Hi JAdams,
so say you have 3 matches, one in sheet A and two in sheet B, what would you like the formula to return? All 3 items in one cell? Or one next to eachother? How many matches are possible?
Koen

 

[JAdams1983]

Thanks for the reply. It's a timetable I'm creating so I'd like it the formula to return an error message so that it can flag when two things have been scheduled at the same place.

 

[Rijnsent]

Hi JAdams,
what about: =COUNTIF(SHEETA!$B$2:$AL$8,"*SW*")+COUNTIF(SHEETB!$B$2:$AL$8;"*SW*") -> if that formula >1 then you know there is something to look at. What you could also do: in a new sheet, put e.g. SW in cell A1 and then in B1 the formula: =IF(COUNTIF(SHEETA!$B$2:$AL$8,"*"&A1&"*")+COUNTIF(SHEETB!$B$2:$AL$8,"*"&A1&"*")>1,"ERROR","OK")
Would that solve your issue?
Koen