Linking multiple spreadsheets to one consolidated worksheet

Is there a way to link multiple worksheets pulling in only the rows that are populated to create one consolidated target worksheet that represents the contents of all of the source worksheets? The number of populated rows in the source files would be subject to change.

The source files all have the same format as each other and I am just looking to consolidate all of the source data into one sheet on the target spreadsheet

A couple of points: We are using Office 2010. The spreadsheets are all in sharepoint.

Thanks for your help,

Matt