ドロップダウンからリストを絞り込んで選択

質問

Excel初心者です。

ドロップダウンリストを作りそこから品目を選んで書類を作る形式を考えています。

マスタなどの別シートから参照してリストを作るのですが（原価などの情報もマスタで一元管理して、作成した書類にも自動で反映させたいので）、リストが100以上の選択肢を持ってしまうので選ぶのが大変面倒になりそうです。そこで考えていたのは、マスタの品目が英語表記なのでその頭三文字をLEFT関数で抽出た列を作っておいて、書類上ではそれを打ち込むことによってリスト上の選択肢を絞り込む（同じ頭三文字を含む品目もあります）形はどうかと思っています。(http://www.geocities.jp/chiquilin_site/data/100714_screening1.html　こちらのサイトを参考にしているのですが、いまいち自分で応用できなくて困っています。)

例えばマスタ上では
　　 A 　　　　 B　　　　　　　
1　 Por 　　 Pork belly
2　 Asp 　　Asparagus
3　 Bee 　　Beef steak
4　 Bee 　　Beef tongue
という具合です。そこで書類上にBeeと入れて、リストからステーキかタンを選べるようにしようかと思っている感じです。どのようにすればできるのでしょうか、教えていただけませんか？

もしその他に、簡単にリストの絞込みができるやりかたでおススメのがあればそれも教えていただければ嬉しいです。

回答No.3

tom04

No.2です。
Excel2007をお使いというコトですと、
入力規則の「リスト」で「元の値」の欄の数式では他のSheetを参照できないはずですので、
Sheet1の使っていない列を利用してみてはどうでしょうか？
↓の画像のようにSheet2のA列2行目以降にデータを羅列しておきます。
（1行目は何らかの項目名を入れておいてください）
尚、リスト表示させる範囲はSheet1のA2～A20セルとしてみました。

仮にD列にリスト候補を表示させるとします。
（1）Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　カーソルが点滅しているところに
↓のコードをコピー＆ペーストし、VBE画面を一旦閉じてください。

Sub Sample1() 'この行から
Dim lastRow As Long, str As String, wS As Worksheet
Set wS = Worksheets("Sheet2")
lastRow = wS.Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
With Worksheets("Sheet1")
str = Selection
.Range("D:D").ClearContents
wS.Range("A1").AutoFilter field:=1, Criteria1:=str & "*"
If wS.Cells(Rows.Count, "A").End(xlUp).Row > 1 Then
Range(wS.Cells(2, "A"), wS.Cells(lastRow, "A")).SpecialCells(xlCellTypeVisible).Copy .Range("D1")
Else
MsgBox "該当データなし"
End If
wS.AutoFilterMode = False
End With
Application.ScreenUpdating = True
End Sub 'この行まで

（2）画面左下のSheet1のシート見出し上で右クリック　→　コードの表示　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　VBE画面を閉じる

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'この行から
'A2～A20セルを対象としています。
If Intersect(Target, Range("A2:A20")) Is Nothing Or Target.Count > 1 Then Exit Sub
Call Sample1
End Sub 'この行まで

（3）Sheet1のA2～A20セルを範囲指定　→　データ　→　データの入力規則　→　リスト　→　「元の値」の欄に
=OFFSET(D$1,0,,COUNTA(D:D))
という数式を入れ　→　同じ画面の「エラーメッセージ」タブ　→　「無効なデータが入力されたら・・・」のチェックを外しておきます。

これでA2～A20セルに何らかの文字（1文字でも構いません）を入力　→　Enter　→　元のセルを選択
これでSheet2のA2セル以降のデータでその文字から始まるデータのみがリスト候補として表示されます。

※　D列が目障りであれば非表示にしてください。m(_ _)m