EXCEL リスト連動 リスト自動拡張

質問

商品の在庫をエクセルで入力します。

商品名（人参、りんご、さんま、片栗粉等）を入力する際に、
INDIRECT関数と入力規則を利用して、
種別（野菜、フルーツ、海鮮類、粉類等）を絞り、
それぞれの商品名をリストから選択し入力できるようにしたい。

新商品を入荷したりするので、元の商品名のデータを追加したり削除したりする時に、自動的に元の商品データのリスト範囲を拡張するようにしたい。

今、ドラッグして範囲指定してリスト範囲を指定しています。
OFFSET関数とCOUNTA関数を使用して、自動的にリストが拡張できるようなのですが、何を入力したらいいのでしょうか？うまくできません。

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.4

msMike · ベストアンサー

[No.3補足]へのコメント、

》 赤枠の範囲内なら、リストに表示されますが…
良くぞ仰った！

だったら、ステップ２の赤枠範囲 B2:E10 の10行を最初から 100行とか1000行とかに広げておけばＯＫ、ということをご理解あれ！

お礼

何度も回答頂きありがとうございました。
この方法で、商品名データ一覧に追加用の空白セルを含めても、ドロップダウンリストに空白が表示されずに商品名だけ選択できるようになりました。

回答No.5

msMike

[No.3補足]へのコメント再び、

》 赤枠の範囲外にも増やして追加した分は、
》 自動的にリストに表示されません。

今からでも遅くない、下記を実行すれば。「範囲外」の最下端が 15行目と仮定して…

２'．範囲 B2:E16 選択 ⇒ Alt+MC ⇒ “上端行”だけにチェック入れ ⇒ [OK]
￣￣「現在の〇〇の設定と置き換えますか？」に対して、全て素直に[はい]、[はい]、[はい]、…と従順に従う

ただ、ソンダケェ～

回答No.3

msMike

添付図参照

１．範囲 B2:E2 選択 ⇒ [名前ボックス]内にマウスカーソルを放り込 ⇒
￣￣ “B2”を“種別”に上書きして ⇒ Enter
２．範囲 B2:E10 選択 ⇒ Alt+MC ⇒ “上端行”だけにチェック入れ ⇒ [OK]
３．セル G2 選択 ⇒ Alt+AVV ⇒ [設定]タブにおいて、[入力値の種類]に
￣￣“リスト”を設定 ⇒ [元の値]ボックス内に次式を入力 ⇒ [OK]
￣￣ =種別
４．セル H2 選択 ⇒ 以下ステップ３に同じ。ただし、次式は下記のとおり
￣￣ =OFFSET($B$2,1,MATCH(G2,種別,0)-1,COUNTA(INDIRECT(G2)),)

こうしておけば、黄色で塗りつぶした空白セル以下(例え 11行目より下方であっても)に食材を追加しても構わないけどォ～・・・(副作用もあるゥ～)

どんな副作用かはご自分で試してみれば分かります。私からは敢えて申しませぬ。(その程度ならOKよ、と仰るかも知れないしィ～)

お礼

具体的な手順まで説明していただきありがとうございました。

補足

赤枠の範囲内なら、リストに表示されますが、赤枠の範囲外にも増やして追加した分は、自動的にリストに表示されません。
数式は=も含めてコピペしました。

回答No.2

kkkkkm

No1です。追加です。
名前定義してるみたいですのでこっちの名前定義のところを参照してください。

セルの参照範囲を可変にする（OFFSET,COUNTA,MATCH）
https://excel-ubara.com/excel3/EXCEL016.html

お礼

2回も回答ありがとうございました。

補足

わかる人にはピンとくるのでしょうけれど・・・

回答No.1

kkkkkm

ここに説明があります。
エクセルでリストの範囲を可変にしてプルダウンリストに連動させる方法！OFFSET関数で可変に
https://whiteleia.com/%E3%82%A8%E3%82%AF%E3%82%BB%E3%83%AB-%E5%8F%AF%E5%A4%89-%E3%83%AA%E3%82%B9%E3%83%88-%E9%80%A3%E5%8B%95-offset/

補足

このページの「同シートにプルダウンリストがある場合、可変範囲をリストに連動させる」で、入力規則を設定した一番上のセルなら希望の操作ができますが、その下のセルはリストの内容がひとつずつずれて表示され、最後には入力していないので空白が表示されます。（一度に複数セルに設定しても、コピペしても同じ）