[[20060920215636]]　『入力規則のリストに先頭が数字の名前が付けられな』（えでたか）　ページの最後に飛ぶ

『入力規則のリストに先頭が数字の名前が付けられない場合』（えでたか）　

 現在ある催し物の参加者名簿を作成しています。↓以下その表の形です。
【名簿（シート１）】
 　　A　　　　B　　　C　　　D　　　　E・・・
 １　会場名　参加日　時間　コース　名前・・・
 ２　東京　　1月1日　1時～　①　　　あああ
 ３　千葉　　2月2日　2時～　②　　　いいい
 ４　埼玉　　1月2日　1時～　①　　　ううう
 ５　東京　　1月2日　1時～　③　　　えええ
【入力規則用のリスト（シート２）】
 　　　A　　　B　　　C　　　D　
 １　東京　1月1日　1時～　　①
 ２　東京　1月2日　1時～　　③
 ３　東京　1月3日　2時～　　②
 ４ 
 ５　埼玉　1月1日　3時～　　③
 ６　埼玉　1月2日　1時～　　①
 ７
 ８　千葉　2月2日　2時～　　②
 ９　千葉　1月3日　1時～　　①　←このリストが90行近く続く
 ・　・　　　・　　・　　　・
 ・　・　　　・　　・　　　・
 90　東京
 91　埼玉
 92　千葉
 催物の回数や会場、時間がバラバラなのでミスを防ぐ為に入力規則を使って
 ドロップダウンで各項から選び、例えばシート１のA２で会場名を選べばB２
 ではその会場で行われる日程のみ選べるようになり C２・D２ではその会場
 でその日付で行われる時間が自動的に入る（またはドロップダウンのリスト
 にそれしか表示されない）ように作りたいと思い、シート2のリストはA列
 （A90～を「会場名」、B１～B80位までの列を「東京」「埼玉」・・と各会
 場の日程毎に名前を付けました。
 シート１のA列にドロップダウンで会場名を選ぶと、東京で行われる日程のみ
 （シート２のB１～B3）の表示できるように、シート１のB列には入力規則で
 「INDIRECT（A２）」としました。
 しかし、この方法で行くとシート2のリストのC列は各日付を名前にしなけれ
 ばならず、名前を数字にするとエラーになってしまいます。
 名前ではなく、直接セルを指定「INDIRECT("Sheet2!C2")」にするとC2の時
 間「1時～」のみが表示されてしまい、「INDIRECT（"Sheet2!C2:C88")」に
 すると、せっかくシート１のBまでは各会場に合った値が出てきたのに、全会
 場の全ての時間が表示されてしまいます。
 勉強不足でご迷惑をおかけして申し訳御座いません。
 どうぞよろしくお願いいたします。

 会場と参加日が決まれば時間が一意的に決まるのなら関数で表示させればいいと思うのですが、
 そうでなければ、かなり面倒ですけど名前を「東京1月1日」と付け、入力規則のリスト範囲を
 =INDIRECT(A2&TEXT($B$2,"m月d日"))　などと付ける方法は考えれますね。
（純丸）(o^-')b　※たたき台でし。

 >会場と参加日が決まれば時間が一意的に決まるのなら～　　…決まるのかも？ってことで、
 INDEX関数を使うとこんな感じでできるようです。
 C2に　=INDEX(Sheet2!C$1:C$9,SUMPRODUCT((Sheet2!$A$1:$A$9=$A2)*(Sheet2!$B$1:$B$9=$B2)*ROW($C$1:$C$9)))
　として、右に下にコピー。
 A列B列どちらかでも空欄だったら空欄、という処理を加えれば、
 =IF(COUNTA($A2:$B2)<2,"",INDEX(Sheet2!C$1:C$9,SUMPRODUCT((Sheet2!$A$1:$A$9=$A2)*(Sheet2!$B$1:$B$9=$B2)*ROW($C$1:$C$9))))
(かなれっと)

 マクロで入力規則を設定してみました。
 入力によりリストの絞込みをしています。
 名簿（シート１）のシートタブ（　＼名簿（シート１）／　）を右クリックし、
 コードの表示を選択、出てきた画面に下記のコードを貼り付けて閉じる。
 リストのシート名はSheet2としていますので、「<--ここ」と記している部分のコードを
 実際のシート名に変更してください。
 (ROUGE)
'----
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim tbl, i As Long, ky, lst As String
    With Target
        If .Count > 1 Then Exit Sub
        If .Row = 1 Then Exit Sub
        If .Column > 3 Then Exit Sub
    End With
    With Sheets("Sheet2")                                           '<--ここ
        tbl = .Range("A1:C" & .Range("C" & Rows.Count).End(xlUp).Row)
    End With
    Select Case Target.Column
        Case 1
            With CreateObject("Scripting.Dictionary")
                For i = 1 To UBound(tbl, 1)
                    If Not IsEmpty(tbl(i, 1)) And Not .exists(tbl(i, 1)) Then _
                        .Add tbl(i, 1), Empty
                Next
                If .Count > 0 Then
                    For Each ky In .Keys
                        lst = lst & "," & ky
                    Next
                Else
                    lst = ""
                End If
            End With
        Case 2
            If Target.Offset(, -1).Value = "" Then Exit Sub
            With CreateObject("Scripting.Dictionary")
                For i = 1 To UBound(tbl, 1)
                    If tbl(i, 1) = Target.Offset(, -1).Value And Not IsEmpty(tbl(i, 2)) And _
                        Not .exists(tbl(i, 2)) Then _
                        .Add tbl(i, 2), Empty
                Next
                If .Count > 0 Then
                    For Each ky In .Keys
                        lst = lst & "," & Format(ky, "m月d日")
                    Next
                Else
                    lst = ""
                End If
            End With
        Case 3
            If Target.Offset(, -1).Value = "" Then Exit Sub
            With CreateObject("Scripting.Dictionary")
                For i = 1 To UBound(tbl, 1)
                    If tbl(i, 1) = Target.Offset(, -2).Value And tbl(i, 2) = Target.Offset(, -1).Value _
                        And Not IsEmpty(tbl(i, 3)) And Not .exists(tbl(i, 3)) Then .Add tbl(i, 3), Empty
                Next
                If .Count > 0 Then
                    For Each ky In .Keys
                        lst = lst & "," & ky
                    Next
                Else
                    lst = ""
                End If
            End With
    End Select
    If lst = "" Then: Target.Validation.Delete: Target.Value = Empty: Exit Sub
    With Target.Validation
        .Delete
        .Add xlValidateList, , , lst
    End With
End Sub

 純丸さん、かなれっとさん、ROUGEさん、ご回答ありがとうございました。
 大変申し訳ないのですが、勉強不足で関数やマクロが良く分からないので、
 純丸さんの案をいただきました。
 追加の質問で申し訳ないのですが、
 例えば純丸さんの例で表のフォーマットを作り、月ごとに違うブックを用意しようとい思い、
 作ったものをコピーしました。
 コピーしたシートはリスト用の物と、表のフォーマットの2枚です。
 しかし、コピーしたものを使おうとすると
 ドロップダウンが使えなくなり、何も表示されなくなってしまいました。
 入力規則を見ると元の値にそのまま「＝会場名」とあり、
 これを閉じようとすると「入力規則は他のブックあるいはワークシートを参照することはできません」
 とエラーになります。
 もう一度F3で挑戦したのですができませんでした。
 お知恵をお貸しください。宜しくお願いします。

『かえでた』

 申し訳ございません。自己解決しました。
 ご回答下さった方々ありがとうございました。

コメント返信：

[ 一覧(最新更新順) ]

[[20060920215636]] 『入力規則のリストに先頭が数字の名前が付けられな』（えでたか） ページの最後に飛ぶ

[[20060920215636]]　『入力規則のリストに先頭が数字の名前が付けられな』（えでたか）　ページの最後に飛ぶ