Displaying Search results (filtering?)

Hi this is my first post here so let me apologise in advance if im doing something wrong.

My problem is I have two worksheets in worksheet 1 I have a bunch of data and I want to be able to filter out to only include relevant results.

Eg. If I search for 'Dog' I want search column A in another sheet and find all full and partial matches then display the entire row of each of these matches.

Can anyone help? please let me know if i didnt explain it clearly

PS im using excel 2007

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

In E1, put what you're searching for, e.g., dog.

In E2 and down, =ISERROR(SEARCH(E$1, A2))

AutoFilter on col E.

Try this:

In E2 of Sheet1, enter formula:

=IF(ISNUMBER(SEARCH(Sheet2!$A$2,A2)),MAX($E$1:E1)+1,"")

copied down.

Then in A5 of Sheet2:

=IF(ROWS($A$5:$A5)>MAX(Sheet1!$E:$E),"",INDEX(Sheet1!A:A,MATCH(ROWS($A$5:$A5),Sheet1!$E:$E,0)))

copied down as far as you desire (rows will show blank if there are no more matches) and across as many columns as necessary.

When you change the text in A2, any items that contain that string from column A in Sheet1, will appear, along with corresponding items in the respective rows.