Search for string in several spreadsheets (without macros)

Suppose I have several rows of data across different spr开发者_运维知识库eadsheets in the same workbook and I want to display (in a separate spreadsheet) only the rows which contain a specific string. How would I do that?

---

Without macros this gets a little clumsy, but one way to do it is with linking and filtering.

In your separate workbook, copy all the rows from the several worksheets that might be of interest and make sure that they are linked to the source workbook or worksheets. (If you are doing all of this from within the same workbook, you don't have to worry about the external linking.)

Then apply filtering to all of these rows, and set up your filter to look for the specific string that you need.

---

To reiterate @Stewbob's comment, there isn't really a 'clean' way to do this using only Excel formulas and little manual work.

As I don't believe OP required the work to be done in a separate workbook, you could just copy/paste all data into an additional worksheet and filter on the desired string should get you the results you're looking for.