INDIRECTと名前の定義でリストを作成

質問

どなたか分かる方教えください。

A1のセルにコードを入力、B1のセルに商品　を入力したいと考えています。
但し、B1の商品はA1で指定したコードの商品郡を入力するようにしたいのです。
コードと商品郡は別シートに作成しています。
別シート）
　　　Ａ　　Ｂ　　Ｃ　　Ｄ　　Ｅ
　1
　2　A　　B　　C　　D　　E　
　3　・　　・　　・　　 ・　　 ・
　4　・　　・　　・　　 ・　　 ・

２行が全てコード、３行以降がＡ～Ｅの商品郡　になります。
３行目以降のセルには関数を入れて 別シート_２ より集計したものになります。
商品郡の数はコードによってバラバラです。

２行目の記入セルを「名前の定義」で「コード」としてあります。
各列を　「名前の定義」、「Ａ」　｛範囲：A3：A150｝
　　　　　　　　　　　　　　　「Ｂ」　｛範囲：B3：B150｝のようにしました。※今後増減するため、セルは多く設定しました
入力シートの A1にリスト範囲"=コード"　B1に"=INDIRECT（A1）"　
リストはでたのですが、空白セルが多いため使いづらく　空白セルを表示させないために

別シート!A1 に ="別シート!A3:A"&COUNTA(A3:A150）　と入力し自分なりに可変するような式を作ったのですが、
入力シート B1に　リスト範囲"=Ａ"とすると　望むようなリストが出来たのですが、
　　　　　　　　　　　リスト範囲"=INDIRECT（入力シート!A1）"　とすると、
元の値はエラー・・・とエラー表示がでて、リストが表示されません。

どのようにしたらリストが表示されるようになりますでしょうか。

もしくは、増減するセル範囲を空白なしに表示させるにはどのようにしたらいいでしょうか。
質問の説明が不十分すぎて申し訳ないですが、分かる方教えていただけたら嬉しいです。

回答No.4

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

できるだけ単純化してみました

別シートにおいて3行目のタイトルと4行目の間に1行挿入します。
挿入した 別シート!A4セルに
=MATCH("",A5:A151,0)-1
右へオートフィルなどして データの個数を計算させます
=COUNTIF(A5:A151,"*?")
のほうが分かりよいかも。

A3:D151セル範囲を指定して
[Ctrl]+[Shift]+[F3]名前の作成
[レ]上端行 - [OK]

[Ctrl]+[F3]名前の定義
名前: コード
参照範囲 別シート!$A$3:$D$3

入力用のシートにおいて
A3セルの入力規則 リスト =コード
B3セルの入力規則 リスト
=OFFSET(INDIRECT(A3),1,,INDEX(INDIRECT(A3),1))

=OFFSET(基準,行数,列数,高さ)
INDEX(INDIRECT(A3),1)
A3セルによって定義された範囲の1行目の値。つまり、データの個数を返します
よって、欲しい行数(高さ)が決まります

OFFSET(INDIRECT(A3),1
A3セルによって定義された範囲(基準)を1行下にずらします

#OFFSET/INDIRECT関数は、シート上で多用しないように。

お礼

お礼が大変遅くなり申し訳ありません。
分かりやすく画像も頂きましたが、内容を理解するのに時間がかかってしまいお礼出来ていないままでした（。。）ｍ
現在色々忘れる時はこの質問の回答をちょくちょく見直させて頂いております。

別の質問で見かけましたら、その時もご回答頂けるとうれしく思います。

回答No.3

某HN クロメート(Chromate)（@CoalTar）

当方ではできたのですがkagakusukiさんの環境ではできなかったようなので確実性はありませんが

エクセル　リストの連動について教えてください。
http://okwave.jp/qa/q7911265.html

を参考にしてください。

#どちらかというと、セルい範囲 INDEX(～):INDEX(～) といった形が好きだったのですが、、、残念(T.T)

お礼

ご回答いただいてありがとうございます。
僕の使用ではINDEXは関係ないようでした。
OFFSETに変えてもできませんでしたし、
みなさんに教えて頂いた方法をテストで
一からリストを作成すると、INDEX関数を使っても
リストは機能していました。

回答No.2

tom04

こんばんは！
横からお邪魔します。

名前定義する場合、数字やアルファベットは極力使用しない方が良いと思います。
たとえば単にアルファベットだと名前定義できない場合がありますので、
今回のようにどうしてもＡ・Ｂ・・・と名前定義したい場合は
_A
のようにアンダーバーを付けておけば問題なくどんなアルファベット（数値）でも名前定義できます。

さて、本題ですが、空白セルをリストに表示させたくない！というコトですので、
ひと手間かけます。
↓の画像で右側Sheetにリスト表示させたいデータがある場合、直接その範囲を名前定義するのではなく
空白セルを表示させないようにしてみてはどうでしょうか？

右側SheetのG3セルに
=IF(COUNTA(A$3:A$150)<ROW(A1),"",INDEX(A$3:A$150,SMALL(IF(A$3:A$150<>"",ROW($A$1:$A$148)),ROW(A1))))
※　数式内のIndex関数の範囲が3～150行目ですが
ROW($A$1:$A$148) となっている部分を注意してください。
（Index関数の範囲指定の行数と　ROW($A$1:$A$148)　←必ず1行目からIndex関数と同じ行数に合わせる！）

これは配列数式になってしまいますので、Shift＋Ctrl＋Enterで確定！
この画面からコピー＆ペーストする場合はG3セルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
これを列・行方向にオートフィルでコピーすると、画像のようになりますので
こちらの表を名前定義してみてはどうでしょうか？

右側SheetのG3～G150セルを範囲指定(G3セルを選択し名前ボックスにG150と入力　→　Shift+Enterで範囲指定されます）
　→　名前ボックスに　_A　と入力し名前定義
同様にすべての列を　_B　_C　_D　_E ・・・
といった具合に名前定義しておきます。

リスト表示させたいA1セルは入力規則のリストから元の値の欄に
=コード

B1セルのリストの「元の値」の欄に
=INDIRECT("_"&A1)
とすれば何とかお望みに近い形にならないでしょうか？

以上、長々と失礼しました。m(_ _)m

お礼

お礼が大変遅くなり申し訳ありません。
分かりやすく画像も頂きましたが、内容を理解するのに時間がかかってしまいお礼出来ていないままでした（。。）ｍ
現在色々忘れる時はこの質問の回答をちょくちょく見直させて頂いております。

別の質問で見かけましたら、その時もご回答頂けるとうれしく思います。

回答No.1

KURUMITO

別シートでのリストの作り方ですが3行目から下方にデータが入力されていますね。3行目を空白にして4行目からデータを入力するようにします。名前お付けるときは3行目の空白セルが先頭になるようにして設定します。
そうすることでB1セルでのリストの表示は3行目の空白から下方に並んで表示されます。最後の行など最初のリスト表示になることはありません。お試しください。

お礼

分かりづらい、質問文なのに返答いただいてありがとうございます。

補足

思うようにいきませんでした。
3行目に行を挿入して別シートのA1には空白行も範囲にするようになっているのですが・・リストがでませんでした。
定義で直接　A3：A100　とすると空白行ごとでるのですが、
これでは、空白が無くても表示されるようでした。

>最後の行など最初のリスト表示になることはありません。お試しください。
最後の行の空白セルが見えなくなるということでしょうか。
もうしわけありません、うまく辿りつけませんでした。