Linking to a column interspersed with empty cells?

I'm trying to link a list of names (Sheet1, column B) with another worksheet (Sheet2). The problem is that column B is not a continuous list of names; there are one, two, or several empty cells between names. Also, the list will change.

Is there a way to have Excel go down through all of column B and return only the values of the cells that have data while ignoring the empty cells? Or perhaps ignore the empty cells until it finds the first row with data in it after which I could use Offset to find the subsequent one?

Any help would be appreciated. Thanks.

You can use SpecialCells to find cells which are not blank, and you could then copy that range which would be contiguous.

Originally Posted by Palmetto

Please provide a more clear explanation of exactly what you are seeking to accomplish.
What do you mean by linking? In what way? Can you not sort the list and force blanks to the bottom and then link the cells (however it is you wanting to do this)?

Your needs might best be served by uploading a sample workbook that exactly duplicates the structure of your workbook (sensitive data removed) and shows a few example of the desired results.

Okay, I attached a very simplified version of what I'm working with. Basically, Sheet1 is a work schedule for a particular day (which is printed at the start of the day, so I can't sort it without messing up the arrangement). I'm trying to create a report on Sheet2 that takes all that data and reorganizes it. I thought if I could get Excel to identify only the rows in Column B with data in them (B2, B5, B7, etc), that I could then use Offset to gather the rest of the data. The problem (as I see it, anyway) is column J-- the projects for a particular worker are often on multiple rows. And workers in different areas have an empty row between them for formatting reasons. Hope that makes it clearer, Palmetto.