b9 =Ship!D2
c9 =Ship!A2 etc

and drag down.

In column A you find the sheetname (manualy input).

It will be a lot easier if you add all data in 1 sheet.