Retrieving rows from multiple workbooks only if conditions met

[cpenticuff]

I work for a medical facility, and can't really provide an example of the files that I am working with, but I did come up with a file that I am attaching that is representative of what I need to do, although the situation is different.

There are four of the types of files that I have attached. Each file is maintained by a different person and new data is added to it each day. Essentially, there is information about a certain person stored accross each row. Each workbook contains multiple tabs, and all contain the same types of information and are formatted identically. I need a seperate file the pulls data from all of the tabs of the four seperate workbooks. The four employees who are maintaining the original workbooks should not have access to the new file. Essentially, I need to copy all of the data from all tabs of all four files and put it into one sheet. I have researched on this site and found some somewhat similiar ideas, but what I am having trouble with is that I need this to update everytime it is opened based on new data that other people have entered into the original files, and I also ONLY want to pull rows out of the other files in which one specific cell of that row meets a certain criteria.

In the "fake" example I am providing: I only care about people who reside in Florida, so I want to copy all rows from all sheets of all four files that contain "Florida" in the "B" column.

I would really love to not have to use VB scripting. Is this possible utilizing simple formulas? If not, I will of course learn to use whatever code or script is necessary. Utilizing formulas would simplify this process a lot for me, even if there is more work in setting up the initial new workbook.

thanks