Copying multiple rows from 5 sheets into 1 sheet based on certain criteria!

Hello!
I am a novice with Excel and need to extract multiple rows from 5 different sheets to one sheet based on certain criteria. Additional data is being entered into the 5 sheets regularly, so I need the solution to auto-populate(dynamic?). I am using Excel 2013, on Windows 7. I have never used anything other than formulas in my spreadsheets.

Here are some more details:
On the 5 sheets are the production details for the individuals on our sales team. The 5 sheets are broken down by the different segments of our business. The landing sheets for the funneled data are labeled for each salesperson. I do not need to tally any of the data, just copy it.

For example:

Cars

John 1/12 GL $2300 No Red
Dave 1/12 GL $2500 No Green
John 1/12 GL $2300 No Red
Mary 1/12 GL $2700 No Blue
John 1/12 GL $2300 No Yellow
John 1/12 GL $9300 No Red

Boats

John 1/12 GL $2200 No Red
Mike 1/12 GL $2300 No Yellow
Mike 1/12 PL $8300 No Green
Bill 1/12 WL $3300 No Yellow
John 1/12 GL $2300 No Red

Bikes

Steve 1/12 GL $2300 No Red
Tom 1/12 YL $2300 No Green
Mary 1/12 GL $2300 No Red
John 1/12 KL $2300 No Yellow
Mike 1/12 ML $2300 No Red


I need to extract all of the listings from John into John's sheet. Cars, Boats, and Bikes are all different sheets. Each sheet's first column is the salesperson's name. Each sheet has the same column headers and the same number of columns.

Please let me know what other information you may need.

Thank you to whomever can help me with this!

Can you post a sample file?

Click on "Go advanced" and the Paper Clip icon to upload a file,

NEW BUSINESS .xlsx


I need to funnel the rows based on the source column, into the source's sheet, from all five categories (commercial, personal, bonds, health, and main street)

See tab "Fran" for sample formula BUT this will only bring in data from "Commercial".

I do not how, using formulae, you can bring in "dynamically" data from 5 sheets using the same method adopted in "Fran". You require all data to be on one sheet from which you could extract all data for "Fran".

Note I had to re-enter "Fran" in the Source column of "Commercial" to get it to match the text "Fran" in the formula (perhaps due to leading/trailing blanks). And many columns in "Fran" needed reformatting to currency

Formula has to be entered with CTRL+SHIFT+ENTER