エクセルのシート間の串刺し？一覧表の作成で

質問

同じフォーマットのシートが複数枚あります（行事名、場所、日時・・などすべてシートのコピーにより作成）。それを新しいシートに一覧表として、Ａ列には行事名、Ｂ列には場所、Ｃ列には日時・・という風に１シートに表示させたいのです。シートが増えていっても一覧表の行は下へ増えていく・・といったような表作成の方法を知りたいのです。
当サイトで調べてみたところ、似たような質問があり解決できる！と思いきや、その回答の中でもまた質問をしたくてモヤモヤしています・・・
その方の回答では
【【もし、私がやるとすると、シート名をSheet1,Sheet2,.と最後に数字をつけていく名前にします。
こうやると、串刺しを入力するシートで、どこかのセルにSheet1と入れて、そのセルをマウスでドラッグすれば以下sheet2,.という一列ができあがります。 これさえすれば、後はIndirect関数で該当のセルを参照すればＯＫです。
たとえば、A1～A10にシート名が入っていて、各シートの C1セルを参照する場合は、
INDIRECT("'" & A1 & "'" & "!$C$1")
をB1に入れて、後はB2～B10にコピーすればいいわけです】】
だそうなのです（勝手に拝借してスミマセン）。すっごくわかりやすいのですが私にはこのINDIRECT関数の入れ方がわからないのです。
関数は探せましたがあとは１文字ずつ手入力（スペースも入れる？）すればよいのでしょうか？ ("'" & A1 ???
またこの方の回答以外でも方法がありましたら教えてください（失礼ですが・・）。ほぼ初心者なのでＶＢＡとかいう難しそうな方法は避けたいですし、アクセスではなくエクセルで作りたいのです。

回答No.3

imogasi

別シートのセルの値をとってくるのはSheet１!c１などとします。
Sheet1のところが沢山あると入力が大変です。その対処法として、Sheet1の部分を変化させるために、A1:A4に人手で変化させ（列挙し）た値をつくり、オートフィル（式複写）の式の相対的変化を使います。この際はシート名をSheetx（ｘ＝１，２，３・・）にする必要はありません。
Sheet1のシート名を京都ｓにし、値（データ内容）は
Ａ１：Ｃ１に
コンサート 京都 2003/11/2
Sheet2のシート名を大阪ｓにし、値は
Ａ１：Ｃ１に
音楽会 大阪 2003/11/23
Sheet3のシート名を神戸ｓにし、値は
Ａ１：Ｃ１に
講演会 神戸 2003/11/29
Sheet4のシート名を姫路ｓにし、値は
Ａ１：Ｃ１に
討論会 姫路 2003/12/3
---------
Sheet5のＡ１：Ａ４にシート名
京都ｓ
大阪ｓ
神戸ｓ
姫路ｓ
と入れます。
－－－－
Sheet6の
Ａ１に=INDIRECT(Sheet5!$A1&"!"&"A1")といれ下へシート数だけ複写
Ｂ１に=INDIRECT(Sheet5!$A1&"!"&"B1")といれ下へシート数だけ複写
Ｃ１に=INDIRECT(Sheet5!$A1&"!"&"C1")といれ下へシート数だけ複写
あと項目数だけ繰り返す。
－－－－－－
（結果）Ａ１：Ｃ４に
コンサート　京都 2003/11/2
音楽会 　大阪 2003/11/23
講演会 　神戸 2003/11/29
討論会 　姫路 2003/12/3
となります。Ｃ列は書式を日付にしておくこと。
シートが増えると複写を増えたシート数だけ下へ引っ張る
必要があります。
----
Sheet6のＢ列、Ｃ列を複写で自動的にやる場合は
Sheet5のＡ１：Ｄ４を
京都ｓ A1 B1 C1
大阪ｓ A1 B1 C1
神戸ｓ A1 B1 C1
姫路ｓ A1 B1 C1
と入れ、（Ａ１等は＋を下へ引っ張ると良い）
Sheet6のＡ１に
=INDIRECT(Sheet5!$A1&"!"&Sheet5!B1)といれ、
Ａ４まで複写する。
その後Ａ１：Ａ４を範囲指定して（したままで）Ｃ１：Ｃ３まで、右下＋を横に引っ張ると良い。
－－－
数百もあってシート名をＳｈｅｅｔ５にリストするのが大変な場合（コピーが効かないので）
ＶＢＡですが
Sub test01()
Dim sh As Worksheet
i = 1
For Each sh In Worksheets
Worksheets("sheet5").Cells(i, "A") = sh.Name
i = i + 1
Next
End Sub
を実行して不要なＳｈｅｅｔ５やＳｈｅｅｔ６を消してください。

お礼

お礼が遅くなりすみません。すごく丁寧に回答していただきありがとうございます。まさに私が作りたいようなものの例を挙げていただき嬉しく思います。
みなさんが教えてくれた回答のおかげで表が完成しました。大変勉強になりました。感謝しております。

回答No.2

maruru01

こんにちは。maruru01です。

No.1の方があらかた説明していますが、少し訂正。
>同じシート内のE1のセルを表示したい場合は、
>それを表示したいセルに=INDIRECT(E1)と入力する
は、正しい数式は、

=INDIRECT("E1")

です。

ちょっと補足説明です。
通常他のセルを参照する場合は、

=A1
=$B$1
=Sheet2!C1

のように入力しますね。
この場合の「=」を除いた部分を文字列として、INDIRECT関数の第1引数に指定すると、同じようにそれぞれのセルを参照出来るのです。
つまり、

=INDIRECT("A1")
=INDIRECT("$B$1")
=INDIRECT("Sheet2!C1")

というわけです。
第1引数に指定するのは、あくまでセル番地を示す"文字列"です。
そして、その文字列が別のセルに入力されているなら、そのセルを指定すればいいわけです。
A1に「Sheet2」と入力されているとすると、

=INDIRECT(A1 & "!$C$1")

とすると、関数の第1引数には、「Sheet2!$C$1」が指定されることになります。
複数に分かれている文字列を繋ぐ時は、「&」を使用します。
この場合はA1自体を参照するのではなくて、A1に入っている「Sheet2」という文字列を使用しているだけです。

さて、最後に、A1の両側に「'」が付いています。
これは、シート名に特定の文字が入っている場合に、これを両側に付けておかないと、シート名と認識してくれないための処置です。
例えば、あるシートの名前を、「1-1」にして、別のシートのどこかのセルに、

=1-1!A1

と入力すると、「ファイルが見つかりません」というタイトルのファイル選択ダイアログが表示され、キャンセルすると、
「#REF!」
というエラーになります。
ところが、これを、

='1-1'!A1

とシート名を「'」で括ると、エラーにならずにちゃんとシート[1-1]のA1の値を表示します。
（ただし、未入力だと「0」になりますが）
これは、「-」がシート名にあるために生じる現象です。
質問の例では、シート名は「Sheet」＋数字なので、「'」で括らなくても問題はないです。
しかし、どんなシート名を入れてもエラーにならないように安全を見て、「'」を付けているのでしょう。

ところで、この質問のNo.は何番でしょう？
なんか自分の回答のような気がするのですが。
（似たような別の人の回答かも知れませんが。）

補足

わかりやすく親身に教えていただき感謝しています。
しかしもう少し教えてください（汗）。
自分の具体例でいうと、Sheet1～9があります。まず、すべてのシートのセルＢ９をSheet10のＢ列に上から下へ表示させたいです。
この場合、まずSheet10のＡ列Ａ１にSheet1と入れドラッグコピーで，Ａ２にSheet2・・・としますよね。
そして教えていただいたとおり、Ｂ１に INDIRECT(A1&"!$B$9")と入力すると きちんと表示されました（ヤッタ☆）。下にドラッグコピーでこれで出来ます♪。が、INDIRECT(Sheet1!$B$9）と入れると（Sheet1へ行ってＢ９を選択） #REF!と出ます。あれ？
とりあえずは先の入力で出来たので解決なんですが・・同じことなんじゃないですか？。なぜでしょう。
あともう一つ、入力方法なのですが、ツールバーの関数貼り付けボタン［ｆｘマーク］から入ると 参照文字列 と 参照形式 を入力する欄があるんですが、私のこの場合それぞれに何と入力したらよいのでしょう？。
質問がすごく込み入って申し訳ございません。実は［’］［”］［!］といった記号が出てくると困惑してるのが正直なレベルです。
拝借させてもらった回答の質問Ｎｏは55739です。maruru01さんは登場されてないようですね。

回答No.1

noname#10474

=INDIRECT("'" & A1 & "'" & "!$C$1")は、

=INDIRECT(A1&"!$C$1")としてオッケーかと思います。

INDIRECT関数は、カッコの中のセルを引っ張ってくる関数です。

たとえば、簡単な例を書くと、同じシート内のE1のセルを表示したい場合は、それを表示したいセルに=INDIRECT(E1)と入力する、ということです。

質問内にある【回答】の場合、
=INDIRECT("'" & A1 & "'" & "!$C$1")は、
A1は、sheet1が入ってますので、つまりは、
=INDIRECT(sheet1!$C$1)になるので、この関数を入力したB1セルには、sheet1のC1セルに入ってる値が表示されるよ、という意味になるのです。

どうですか？

参考URL：

http://oshiete1.goo.ne.jp/kotaeru.php3?q=55739

お礼

早速の回答に関数の説明などわかりやすくしていただきありがとうございます。【回答】にもある［"］［'］とかって、法則があるんでしょうけど難しいですよね。またよかったら意見くださいね。