エクセル2013です。
Sheet1にドロップダウンリストを作成しました。
Sheet2のC列を参照したい為
Sheet2のC3~C20000に名前の管理で「商品名」としておき
Sheet1の入力規則には
リスト-「=商品名」
としました。
ただ、現在Sheet2のはC列にはまだ1,000行ぐらいまでしか値が
入っておらず、ドロップダウンリストでは空白の方が多く
選択が大変です。
Sheet2のC列には毎日入力されて値のある行が
日々増加していくので都度入力規則のリストのデータ範囲を修正するのは
大変なので
C3~C20000としておきました。
NETで調べると、リストの範囲が同一シート内はoffsetを使えばできるみたいですが
リストが他シートの場合はどうすればいいのでしょうか?
何かいい方法はありますでしょうか?
よろしくお願いします。
No.1ベストアンサー
- 回答日時:
ご使用のExcelのバージョンが2013なら、他シートのデータ範囲でも入力規則のリストに設定できるはずですが・・・
また、大きめのデータ範囲で設定しても空白セルを除外してリストに表示されるはずです。
もし、Excel2003をご使用なら、「名前の定義」でデータ範囲を指定するOFFSET関数を適当な名前を指定して、この名前をリスト範囲に「=定義した名前」のように設定すれば、他シートの範囲のリストも参照することができます。
この回答への補足
ありがとうございます。
>ご使用のExcelのバージョンが2013なら、
>他シートのデータ範囲でも入力規則のリストに設定できるはずですが・・・
はいできます。
別シートSheet2のC3~C20000の範囲を
を名前の管理で「商品名」としておいて
Sheet1のドロップダウンリストの
入力規則にはリスト-「=商品名」
としました。
名前を「商品名」とした範囲のSheet2のC3~C20000のうち
C3~C1000までしかデータが入力されていません。
Sheet1のドロップダウンリストをクリックすると
Sheet2のC列に入力してある値がリストに並びますが
1000行中にランダムに空白が有りますがそれも表示されてしまいますし
下の方に行くとリスト内がずっと空白です。
>また、大きめのデータ範囲で設定しても空白セルを
>除外してリストに表示されるはずです。
これが今の私の方法では除外されないのです。
セル範囲に名前を付けない方がいいのでしょうか?
すいません。
ずっと2003のみ使用していて
エクセル2013は今年4月から使っております。
今試したら
名前の管理を使わなくても
ドロップダウンリストに
リスト
↓
=Sheet2!$C$3:$C$20000
が出来ました。
大変申しあわけ有りません。
ありがとうございました。
No.3
- 回答日時:
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
- 回答日時:
こんにちは!
リストで空白セルを含む範囲を指定すればそのまま空白セルがリスト候補になってしまうと思います。
そこで一例です。
使っていない列に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
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excel ドロップダウンリスト(入力規則)に関してです データの入力規則で元データ79000行のド 3 2023/07/17 10:06
- Visual Basic(VBA) VBA ドロップダウンリストを残して値のみクリア 2 2022/10/27 05:42
- Excel(エクセル) エクセルで値ではなく関数を参照する方法 6 2023/03/19 00:50
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
- Excel(エクセル) VLOOKUP が機能しない、その原因は何 ? 8 2022/10/19 12:06
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- Excel(エクセル) Excel 値を返す数式についてです 3 2022/11/21 20:08
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Excel(エクセル) データ入力規則リスト 空白を無視 3 2022/07/13 15:11
- その他(プログラミング・Web制作) pythonでクラスで複数のメソッドを利用する方法 2 2022/04/15 04:17
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルでの作業計算方法について
-
はがきについて。
-
エクセル 文字を増やしたい。
-
セルの内容表示が邪魔になる
-
Microsoft365に変えたのですが...
-
エクセルの計算
-
Microsoft1Officeの互換ソフト...
-
【マクロ】その時、その時で変...
-
【マクロ】読取専用のファイル...
-
エクセル初心者です 関数の入れ...
-
Excel ピボットテーブルで日付...
-
【関数】適切な文字数の数字を...
-
LOOKUP関数を使えばいいのでし...
-
Aというブックの1というシート...
-
エクセル関数を教えてください
-
Excelのチェックボックスの使い...
-
エクセル 白黒印刷で白線を印刷...
-
時間によってファイル名が変わ...
-
WPS OFFICEでの縦書きについて
-
エクセルの条件付き書式につい...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報