エクセルでの抽出処理について

質問

環境：windows7　EXCEL2010

社内で物流業務を行っているのですが
Excelのシート1内には

列に項目（品番　品名　発注数　発注バラ数　出荷先）
行に（各数値、名称）
が入っているのですがこれをもとに出荷表をある程度の整形をしたいと思っています。
（今までは手動でしたが対応に手間がかかる為）

具体的には
シート2以降に同じレイアウトで印刷したいフォーマットに作っておきます
（最大20社ほどなのでこのレイアウトはあらかじめ作成しています）

シート一つにつき出荷先一つ作る前提で
出荷先Ａ　の内容をシート2（出荷先Bはシート3・・・出荷先Cはシート4）に転記したいと考えております。

・シート2の具体的な転記
見出し（出荷先を代入）があり
列には　品番　品名　発注数　発注　バラ数　
行には列に対応した出荷先の数量
を入れたいと思っています。

問題なのが
出荷先が行列にＡＡＡＡＡＡ、ＢＢＢＢＢＢ、CCCの様にバラバラではなく連続して並んでいるのですが
発注する品目が増えると行数が増える為、転記するにも起点となる座標がなくて関数をどうしたらいいかと見出しの代入から含め正直わからない所です。

ここからどのようにしたらよろしいでしょうか？
よろしくお願いいたします。

※画像添付しておりますがVBAにていらない行列は削除する処理はいれており
列の項目については記載しました内容のみとなっております。

回答No.1

MackyNo1

添付画像が小さくてレイアウトがわかりません（何のデータも入っていないなら添付画像でなくとも文章で説明できるような気がします）。

確認ですが、出荷先の列のデータが連続して入力されている条件で、この出荷先のデータを抽出したいということでよいのでしょうか？
また、シート名が出荷先ということでよろしいのでしょうか？

このようなケースなら、関数を使ってシート名から出荷先を取得して、この出荷先のデータの行範囲をMATCH関数とCOUNTIF関数で位置とデータ数を特定して、OFFSET関数で対象セルを取得すればよいことになります。

たとえば出荷先は以下の数式で取得できます（ただしそのブックを保存する必要があります）。

=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

最終的に以下の数式をA列に入力して下方向および右方向にオートフィルすればご希望のデータが表示されていると思います。

=IF(COUNTIF(Sheet1!$F:$F,RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))))<ROW(1:1),"",INDEX(Sheet1!$A:$N,MATCH(RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))),Sheet1!$F:$F,0)+ROW(1:1)-1,COLUMN(A:A)))