excel index関数？changeイベント？？

質問

作者不明のため、教えていただきたいのですがお願いします。

シートが４つほどあり、index関数でデータを５つめのシートで集計しています。

集計シート例：
　Ａ　　Ｂ　Ｃ　Ｄ　Ｅ　Ｆ　Ｇ　Ｈ　・・・
１　　　【ここにドロップダウンのボックス？があります▼】
２　　　あ　い　う　え　・・・
３●●　１　１　２　２　・・・
４△△　２　２　３　２　・・・
５□□　１　１　１　１　・・・
・
・

●●シート例：
　Ａ　　Ｂ　Ｃ　Ｄ　Ｅ　Ｆ　Ｇ　Ｈ　・・・
１　　　あ　い　う　え　・・・
２ＡＡ　１　１　１　１　・・・
３ＢＢ　０　１　１　１　・・・
４ＣＣ　１　２　２　２　・・・

あいうえ項目が各シートにあり、集計シートでドロップダウンリスト（リストの下のセルＢ１には数字の割り当てがあります）からＡＡを選ぶと各シートからデータを引っ張ってきて（index関数がＢ１の数字を読み取ります）、各シートのデータが集計されます。

ＢＢやＣＣと選んでも簡単に集計してくれるので重宝していたのですが、
これを応用しようと新規ブックで似たものを作り、
index関数（例：=IF((INDEX(●●!$B$2:$H$10,$B$1,1))="","*",INDEX(●●!$B$2:$H$10,$B$1,1))）
※列番号も右セル（あ→い→う・・・）にいくごとに増えていきます。
もコピーしましたが「#REF!」となって集計してくれません。
元のブックを開いてドロップリストを右クリック→マクロの登録をみるとマクロ「ドロップ1_change」という登録が見えますが、マクロを調べても表示は
Sub ドロップ1_Change()

End Sub
しかでてきません。
なにも入っていないのになぜ動くのでしょうか？
また他のブックではなぜ「#REF!」となるのでしょうか。

質問もどうしていいかわからないためわかりにくいかと思いますが、教えてください。この説明で不足している場合はご連絡下さい<(_ _;)>

回答No.2

Wendy02

こんばんは。

私の使ったマクロではダメでしたか？
というか、分らなかったのかもしれませんね。関数を使わない分だけ、私には楽で易しいと思います。

＞楽に集計して、選択しなおしても一括で見れるようにする関数や方法

たぶん、そういうお話なら、今の路線を直していくのがもっとも早いのではないか、と思います。易・難の区別は、このレベルではつけられません。

おそらく、
=IF((INDEX(●●!$B$2:$H$10,$B$1,1))="","*",INDEX(●●!$B$2:$H$10,$B$1,1))）
　　　　　　↑
　　　　　●●に現在のブックの正しいシート名を入れる、それが唯一の解決策だと思います。Sheet1 となっていたら、Sheet1 と入れるわけです。

お礼

変身が遅くなり申し訳ありませんでした。
教えていただいたマクロをさわってみたり、いろいろやってはみましたが、自力では無理と判明。
もうすこし自分の力に見合った方法を探してみようと思います。
ご協力、本当にありがとうございました。

回答No.1

Wendy02

こんばんは。

ご質問のご説明では、私には分りません。
たぶん、ドロップダウンリストではなくて、フォームツールの・コンボボックスだと思いますが、一番大事なことは、どういう状態から、「何をしたいのか」ということです。

他人の作ったものは、しょせん、他人のものでしかないのです。だから、複製を作るのはは諦めて、新しく作るつもりのほうがよいです。（というよりも、自分で作ったものでも、同じことです。。半年経ったものなどは、新たに作ったほうが、だいたいは良いものが出来ます。）
----------------------------------

＞=IF((INDEX(●●!$B$2:$H$10,$B$1,1))="","*",INDEX(●●!$B$2:$H$10,$B$1,1))）

おそらくコンボボックスのプロパティのコントロール－リンクするセルは 「$B$1」 で、入力範囲は、どこかに、項目名があって、そのリストの数は、１から９までのはずです。

＞「#REF!」となって集計してくれません。
理由はわかりません。何が違っているかは、ご自身で探しだしてください。おそらく、集計シートの「B1」に、範囲以上の数字（例:10)が入っているか、シート名が違っているか、範囲が違っているか、のどれかです。

＞Sub ドロップ1_Change()

＞End Sub

が、空なのは、本人は作れなかったからだと思います。マクロが作れたのなら、Index関数は必要がありません。コンボボックスで、1～9までが出るようにする、という条件で、リンクするセルは、上記で書いたように、「B1」という条件です。（ただし、以下の場合は、出力は、シートの並んでいる順に出てきます。）

Sub ドロップ1_Change()
'
Dim i As Long
Dim j As Long
j = Range("B1").Value - 1
For i = 1 To 4　'シートは4枚。
With Worksheets(i)
　'各シートは、B列からH列までで。
　.Range("B2").Offset(j).Resize(, 7).Copy Range("B3").Offset(i - 1)
End With
Next
'
End Sub

中身を抜き出してお使いください。

補足

回答、ありがとうございます。
したいことについてですが、
集計時に、AAやBBなどの項目をコンボボックスでもなんでもいいのですが、
選択するたびに各シートからデータを引っ張ってきて、各シートのAAやBBの項目を●●、△△別に見たい、というものです。
別ブックで同じ状況にしても出来なかったというのは、コンボボックスでAAからBBに選択しなおすと「#REF!」になってしまうのです。
もっと楽に集計して、選択しなおしても一括で見れるようにする関数や方法があるのでしたらご教授いただきたいのですが。。。