エクセル データの入力規制「リスト」でこんな事できますか？

エクセルでこんな事できますか？
並列したQ列　R列があり、
また、添付には表記されていませんが、
その隣にS列　T列があり、
Q列、S列は、「コード」という事で、
Q列は１，２，３，４，５，６，７，８，９，１０
S列は１１，１２，１３，１４，１５，１６，１７，１８，１９，２０
です。
R列は、「材質」でQ列に対応し、添付ファイルのように１０項目
T列も「材質」でS列に対応し、１０項目あります。

R列　T列「材質」を見て　Q37セルにコードを入力したく、
その時、ドロップダウンリストとして、R列、続けて、T列の項目を表示したいです。

データの入力規制「リスト」で、できそうかと思い、頑張っていたのですが、
うまくいかず困っています。
データの入力規制「リスト」にはこだわりませんが、
マクロはできるだけ使わず完成したく思います。
エクセル詳しいかたご教授よろしくお願いします。

質問者からの補足コメント

• 

  わかりにくい内容ですみません。
  本当は、２０品目　縦に並べたかったのですが、
  諸所の事情により、２つにわけた、、、でわかりますか？
  それでも、ドロップダウンリストには、20品目表示したいといった事なのですが、、、
  それで、ドロップダウンリストの２０品目から品物を選んで、
  Q37セルには、その品目に対するコード（数字）を入れたい
  という事ですが、わかりますか？

  
    補足日時：2019/04/01 00:12

• 

  2列に　わたる、
  リストを、
  プルダウンで　全て、
  表したい、、、その通りです。
  
  例えば、
  プルダウンで　ブリンを、
  選べば、
  Q37に　2を、
  入れたい、、、その通りです。
  
  Q36から続く「選択コード」に入る入力値は、整数で、
  別のシートで、整数を使った　計算式　関数に使います。
  
  なぜ、こんな事をしたいかというと、
  「材質」を参照して、Q36から続く選択コードの列には、対応するコード（整数）を入力するのだけど、
  ふだん、34行から上の行は、表示されておらず、
  その上、この選択コードが、最大で500行程あり、
  記憶だけだと、入力間違いの恐れがあり、
  こんなことできたらな～と思った次第です。

  No.6の回答に寄せられた補足コメントです。 補足日時：2019/04/01 10:50

• 

  2列に　わたる、
  リストを、
  プルダウンで　全て、
  表したい、、、に関しては、作業列を作成する事にしました。
  
  例えば、
  プルダウンで　ブリンを、
  選べば、
  Q37に　2を、
  入れたい、、、に関しては、何とかならないものでしょうか？

  No.8の回答に寄せられた補足コメントです。 補足日時：2019/04/01 12:01

• 

  今回の質問においては、プルダウンリストでは文字列の材質(リンゴとか)だけど、それを選択すると同セルでデータがそれに対応するコードに変わる、、、です。
  
  、、、で、各々の先生の回答を拝見させて頂き、無理かも、、、と思い出して、
  文字列を選択したら別セルにコードが出る、、、も挑戦していますが、
  実は、うまくいっていません、です。

  No.10の回答に寄せられた補足コメントです。 補足日時：2019/04/01 19:55

• 

  質問の範囲から外れてすみません、お言葉にあまえ、
  何を試してかというと、
  VLOOKUP　リストダウンした項目から、文字列から、コードの選択、
  また、　材料名の前にコードをつけても、一応試しています。
  、、、が、
  VLOOKUP　LOOｋUPによる、文字列からコードを選択は、
  リストの作成事情から、
  やはり、どうもしっくりきません。（できないわけではないが、使い勝手がわるい）
  また、材料名の前にコードをつけるは、確かにできますが、
  全体の使い勝手から、すみませんが、採用できませんでした。
  結局、無いものねだりみたいになるのですが、
  要は、材料をチョイスして、コードを入力が目的で、
  その際、間違いが無いように入力する際に材料のリストを表示したいだけなのですが、、、
  どのような方法がよいかの方針が見つからず、うまくいっていません。

  No.11の回答に寄せられた補足コメントです。 補足日時：2019/04/01 21:07

No.13 回答者： fujillin 回答日時： 2019/04/02 11:27

No9です

回答の意味が通じている雰囲気はあまりなさそうですね。

やる価値があるのかどうかわかりませんが、以下は無理やりの実現方法の一例です。
ただし、まったくのブラックボックスになると思いますので、方法のみ。

1）対象シートのタブを右クリックして出るメニューから「コードの表示」を選択。
2）上記で表示される、コードのエディタ欄に以下のコードをコピペ。

以上で、ご希望のような動作になるものと思いますが、メンテも不可能となることでしょう。
（↓の1行目の対象範囲の文字列「Q36:Q40」を、実情に合わせて修正してください）

※ まず、実物ではなくテスト用のブックで試してみてください。

'--------- ここから --------
Const targetRange = "Q36:Q40" ' ←プルダウンの対象範囲
Dim flag As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
　If flag Then Exit Sub
　lText = ""
　For c = 0 To 2 Step 2
　　For r = 0 To 9
　　　lText = lText & "," & Range("R25").Offset(r, c).Value
　　Next r
　Next c
　lText = Mid(lText, 2, Len(lText))
　With Range(targetRange).Validation
　　.Delete
　　.add Type:=xlValidateList, Formula1:=lText
　End With
　flag = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
　If Intersect(Target, Range(targetRange)) Is Nothing Then Exit Sub
　v = Target(1).Value
　For c = 0 To 2 Step 2
　　For r = 0 To 9
　　　If v = Range("R25").Offset(r, c).Value Then
　　　　Target.Value = Range("R25").Offset(r, c - 1).Value
　　　　Exit Sub
　　　End If
　　Next r
　Next c
End Sub
'--------- ここまで --------

この回答へのお礼

ありがとうございます。参考にさせて頂きます。

お礼日時：2019/04/02 11:32

No.12 回答者： ママチャリ 回答日時： 2019/04/01 21:43

ここまでしてやるべきかどうかは疑問なのですが、こんなのはどうでしょうか。

右下の図を見て下さい。B2が選択された状態ですが、数式バーは、1となっています。これは、書式設定のユーザー定義で、「りんご」としているだけで、実際の値は１です。B3、B4セルも同じように設定しています。このB2:B4でリストを作成すると、上の図のようなリストが表示できます。
このリストから「りんご」を選択すると「１」が入ります。

No.11 回答者： chonami 回答日時： 2019/04/01 20:11

そうですね。

前にも書きましたが入力規則のリストはあくまでも入力補助です。
別セルにコードを出す方がいいと思います。

作業列でリストを作成するようにしたなら、VLOOKUP関数は調べましたか？
また、材質名の前にコードをつけて取り出す方法も書きましたが、それは試しましたか？

>実は、うまくいっていません、
何を試してどのようにうまくいかなかったかを書いていただかないと、回答側にはわかりません。

この回答へのお礼

色々ためしてみたのですが、最終的に別セルにコードと材質を結合し、
リストには、その結合したリストを表示させ、
LEFT関数にて頭をとりだし、整数化して　コードとする事にしました。
色々、ありがとうございました。

お礼日時：2019/04/02 11:37

No.10 回答者： chonami 回答日時： 2019/04/01 19:47

えっと、確認なんですがやりたいのはプルダウンリストでは文字列の材質(リンゴとか)だけど、それを選択すると同セルでデータがそれに対応するコードに変わる、ということですよね？

ではなく、文字列を選択したら別セルにコードが出る、ですか？
その点を明確にしていただけますか？

No.9 回答者： fujillin 回答日時： 2019/04/01 17:38

No5です

＞表したい、、、に関しては、作業列を作成する事にしました
＞Q37に　2を入れたい、、、に関しては、何とかならないものでしょうか？

作業列を作成するのなら、いっそのこと別シートにコードと材料の対応表（もちろん全部が縦列に並ぶもの）を作成してこれを「材料・コードのマスターデータ」にするというように発想を変えれば、全体を簡単にできる可能性があります。
（別シートと書きましたが、同じシート内でも良いです。作業列ではなくそちらがメインデータになります。）

質問者様がメインとお考えの（質問文にご提示の）表は、「入力のための表」と考えて、表示されているコードや材料はマスターデータを参照して表示する仕組みにしておきます。

ご質問のプルダウンも直接マスターデータを参照する形にすれば、「入力規則」で簡単に実現できることがお判りでしょう。
プルダウンの値からコードに変換するのも、マスターデータで検索すればLookup系の関数でそのまま求めることができます。
また、メンテナンスなどが必要な際には、必ずマスターデータの方を変更するようにすれば、きちんと全体に反映されるようになるはずです。

使い方も見た目も変わりませんけれど、別シート（または同じシート内）にデータ表を作成して発想をちょっと変えるだけで、ずいぶんと処理が簡単になると思いませんか？

No.8 回答者： nouble1 回答日時： 2019/04/01 11:54

出来ないのは　言いませんが、

作業列を　持ちいず、
式のみで、

複数リストの、
結合を　試みる事は、

エクセルにおける、
最高難易度の　一つと、
思います。


敢えて、求めますか？

No.7 ベストアンサー 回答者： chonami 回答日時： 2019/04/01 11:20

あー…

入力規則はあくまでも、データ入力を補助するための機能ですからリストと違う文字を反映させることはできませんよ。

例えばリストデータは、01りんご・02プリンのように作って置いて、コードを出す時に
=LEFT(Q37,2)*1
と入れておくとか？

No.6 回答者： nouble1 回答日時： 2019/04/01 10:22

あぁ！！

2列に　わたる、
リストを、
プルダウンで　全て、
表したい、

ですか？


例えば、
プルダウンで　ブリンを、
選べば、
Q37に　2を、
入れたいのですよね？


でも、
Q36の　入力で、
どの様な　影響を、
求めるのかが　判りませんね。


示して　頂けますか？

この回答へのお礼

ありがとうございました。

お礼日時：2019/04/02 11:37

No.5 回答者： fujillin 回答日時： 2019/04/01 09:53

こんにちは

＞データの入力規制「リスト」で、できそうかと思い～～
直接そのままでやろうとすれば、「リストは1行又は1列でなければダメ」と叱られると思います。

無理やりやるのであれば、どこかの空いている列を作業列として1列に連結しておけば良いでしょう。
二重データにしたくなければ、作業列側を関数での参照にしておくことで、元のデータを変更しても自動で整合が取れるようにすることは可能です。

とは言うものの、普通に想定されるデータ構造ではないので、その材料からコードを求めるのも、単純に関数で一発というわけにはいかなくなりますね。（それほど、大したことにはなりませんが）
他にも、いろいろと関数で処理したりしようとすると、常にその部分がネックになって、関数式が面倒なることが予想されます。

この回答へのお礼

そうなのです。リストをつかい、色々、関数をつかい、、で、試してみたのですが、色々、制約、制限が発生してうまくいきません。入力において、おおちゃくをしようとして、なお、難解になったといった雰囲気です。

お礼日時：2019/04/01 10:09

No.4 回答者： chonami 回答日時： 2019/04/01 06:08

>諸所の事情により、２つにわけた

レイアウトの問題なら、別の場所に1列に入れてそれをリストにすればいいのでは？
コードも並べて2列で作っておけばVLOOKUP関数などでできますよね？
離れた場所に入れるとか非表示にするとかすればいいかと。
見えている範囲でしかリストを作ってはいけない訳ではありません。