ドロップダウンリストで空白のセルを非表示

質問

エクセル2013です。

Sheet1にドロップダウンリストを作成しました。

Sheet2のC列を参照したい為
Sheet2のC3～C20000に名前の管理で「商品名」としておき
Sheet1の入力規則には
リスト－「=商品名」
としました。

ただ、現在Sheet2のはC列にはまだ1,000行ぐらいまでしか値が
入っておらず、ドロップダウンリストでは空白の方が多く
選択が大変です。

Sheet2のC列には毎日入力されて値のある行が
日々増加していくので都度入力規則のリストのデータ範囲を修正するのは
大変なので
C3～C20000としておきました。

NETで調べると、リストの範囲が同一シート内はoffsetを使えばできるみたいですが
リストが他シートの場合はどうすればいいのでしょうか？
何かいい方法はありますでしょうか？
よろしくお願いします。

回答No.1

MackyNo1 · ベストアンサー

ご使用のExcelのバージョンが2013なら、他シートのデータ範囲でも入力規則のリストに設定できるはずですが・・・

また、大きめのデータ範囲で設定しても空白セルを除外してリストに表示されるはずです。

もし、Excel2003をご使用なら、「名前の定義」でデータ範囲を指定するOFFSET関数を適当な名前を指定して、この名前をリスト範囲に「=定義した名前」のように設定すれば、他シートの範囲のリストも参照することができます。

お礼

すいません。
ずっと2003のみ使用していて
エクセル2013は今年4月から使っております。
今試したら
名前の管理を使わなくても
ドロップダウンリストに

リスト
↓
=Sheet2!$C$3:$C$20000

が出来ました。

大変申しあわけ有りません。
ありがとうございました。

補足

ありがとうございます。

>ご使用のExcelのバージョンが2013なら、
>他シートのデータ範囲でも入力規則のリストに設定できるはずですが・・・

はいできます。
別シートSheet2のC3～C20000の範囲を
を名前の管理で「商品名」としておいて
Sheet1のドロップダウンリストの
入力規則にはリスト－「＝商品名」
としました。
名前を「商品名」とした範囲のSheet2のC3～C20000のうち
C3～C1000までしかデータが入力されていません。

Sheet1のドロップダウンリストをクリックすると
Sheet2のC列に入力してある値がリストに並びますが
1000行中にランダムに空白が有りますがそれも表示されてしまいますし
下の方に行くとリスト内がずっと空白です。

>また、大きめのデータ範囲で設定しても空白セルを
>除外してリストに表示されるはずです。

これが今の私の方法では除外されないのです。
セル範囲に名前を付けない方がいいのでしょうか？

回答No.3

tom04

No.2です。
たびたびごめんなさい。

＞Sheet2のC列を参照したい為
を見逃していました。

画面左下のSheet2のSheet見出し上で右クリック　→　コードの表示　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペーストしてください。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
Dim lastRow As Long
If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
Range("D:D").ClearContents
Range("C3:C20000").Copy Range("D1")
lastRow = Cells(Rows.Count, "D").End(xlUp).Row
Range(Cells(1, "D"), Cells(20000, "D")).SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
End Sub 'この行まで

次にExcel画面（Sheet1）に戻りリスト表示させたいセルを範囲指定し
リストの元の値の欄の数式を
=OFFSET(Sheet2!$D$1,0,,COUNTA(Sheet2!$D:$D))
としてください。

※　前回は同一Sheetでの操作と勘違いしていました。
どうも失礼しました。m(_ _)m

お礼

いつもいつもご配慮いただきすいません。
ありがとうございます。
名前の管理でなくてもできるみたいなので
関数で挑戦してみます。
マクロはマクロで考えたいです。

回答No.2

tom04

こんにちは！

リストで空白セルを含む範囲を指定すればそのまま空白セルがリスト候補になってしまうと思います。
そこで一例です。
使っていない列にC3～C20000セルの空白以外を表示させ、
その列をリストの候補に表示させてみてはどうでしょうか？（VBAも併用します）

仮にD列に表示させるとします。
画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面のカーソルが点滅しているところに↓のコードをコピー＆ペースト

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
Dim lastRow As Long
If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
Range("D:D").ClearContents
Range(Cells(3, "C"), Cells(20000, "C")).Copy Range("D1")
lastRow = Cells(Rows.Count, "D").End(xlUp).Row
Range(Cells(1, "D"), Cells(lastRow, "D")).SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
End Sub 'この行まで

次にExcel画面に戻り　入力規則のリスト表示させたいセルを選択　→　リストの元の値の欄に
=OFFSET(D$1,0,,COUNTA(D:D))
という数式を入れます。

これでD列に表示されているものだけがリスト候補として表示されると思います。

※　D列が目障りであれば非表示にしておきます。

こんな感じではどうでしょうか？m(_ _)m

お礼

やマクロでないとむずかしいでしょうか？
ありがとうございます。