Lookup only non null values

Hi,
I've tried to find similar examples for my problem but can't find any.
My scenario is that I have a file with many worksheets, data of which I want to consolidate into one summary sheet. I will then use the summary to export data (in strings) to a database.
I have added an attachment that should explain what I'm trying to do. See worksheet 'Rank' as the summary sheet.
Loosely defined what I want to do is: If there is no data in the cell, move to the next in the range. Where there is data then add to each column in the 'Rank' spreadsheet.
I've tried looking at the Formula's tab (I'm using Excel 2007) but can't find anything that works.
I'm no VBA boffin - so that's where I'm stuck!
Any help much appreciated.

not enough info, what should second third,fourth look like?

Ok, I've added in some more lines that should help.
Thanks.

sory mate that's a bit beyond me
HELP..........anyone ?