選択したsheetの内容を表示

質問

「sheet1」の「A1」で(sheet名)をドロップダウンから選択すると
「sheet1」の「B列」に、選択したsheetの「B列」の内容が表示されるようにしたいのですが、

1.「sheet1」の「B列」に入る式。
2.「sheet1」の「A1」にsheet名のドロップダウンを表示するためのリストを
　 sheetが増える毎に自動的にリストに加わるようにする方法。

を教えて下さい。

WinXP Excel2003です。

回答No.1

maruru01 · ベストアンサー

こんにちは。maruru01です。

シート名リスト（Sheet1は除く）をSheet1のC列に入れておくとします。
まず、メニューの[挿入]→[名前]→[定義]で、
[名前]：適当な名前（仮に「シート名一覧」とする）
[参照範囲]：「=REPLACE(GET.WORKBOOK(1)&LEFT(NOW(),0),1,FIND("]",GET.WORKBOOK(1)&LEFT(NOW(),0)),)」
と名前を定義します。
次に、C1に、

=INDEX(シート名一覧,ROW(C2))

と入力して、下の行へコピーします。
これ以上はシートは増やさない、という予想最大シート数分コピーして下さい。
シートが存在しない場合は、
「#REF!」
と表示されますが、無視して下さい。
（なんなら、このC列を非表示にしておいても構いません。）
そうしたら、A1の入力規則で、
[入力値の種類]：「リスト」
[元の値]：「=OFFSET($C$1,,,COLUMNS(シート名一覧)-1)」
と設定します。
これで、A1でSheet1以外のシートを選択出来るようになります。
シートを挿入したり、削除したりしても選択リストに反映されます。
そして、B1に、

=IF(LEN(INDIRECT("'"&$A$1&"'!B"&ROW())),INDIRECT("'"&$A$1&"'!B"&ROW()),"")

と入力して、下の行へコピーします。
これも十分な行までコピーしておきます。
（あまり行数が多いと、重くなりますが。）

お礼

とても上手く行きました。
大変使いやすい表ができたと思います。
有難うございました。

回答No.2

imogasi

（テスト・データの用意）
Sheet1のA1にデータ－入力規則－リスト－元の値をF1:F４とし、F1:F4に
Sheet2
Sheet3
Sheet4
Sheet5
と入れる。F1にSheet1と入れて、＋ハンドルを下へ引っ張れば簡単。
これでA1をクリックすると▼が出て▼をクリックして
選択すると、A1にSheet2などがセットされる。
－－－－－
Sheet2のB1:B4に
a2
b2
c2
d2
・・
Sheet3のB1:B4に
a3
b3
c3
d3
・・
以下Sheet4,Sheet5もB列に値を入れる。
（関数式）
Sheet1のB1に
=INDIRECT($A$1&"!"&"B"&ROW())
と入れて、B2以下に複写する。
B１をB2以下に複写する。
（結果）
Sheet2を選んだ時は
B列は
a2
b2
c2
d2
・・・
になる。
シート名を番号数字なしに変えたとき（１月分とかのように）もF1:F4をそれに合わせて変えればよい。
それと各シートでB列最下行がバラバラな場合は
０が出てしまうが。