Create a Loop to check cells between workbooks
 

I have 8 linked workbooks, the user types names in
certain fields within those workbooks. As text is typed
in those 8 workbooks, I need to transfer the typed text
to a 9th workbook. If there is no text in the cell that
should transfer, then excel does not transfer the empty
cell, but continues to search the other workbooks until
text is found to insert into the 9th workbook. If text
if found in one or more of the workbooks, then excel
inserts the additional text in the next available cell in
workbook 9. Is this possible? If so, how do I input a
formula to make this happen?