入力の制限（EXCEL)

質問

EXCEL(2003）にて
シート『仕入れマスター』に
　　A　　　　　　B　　　　　C
1　仕入れ先　商品名　・・・
2　Ａ社　　　　　　ＺＺＺ　・・・
3　Ｂ社　　　　　　ＹＹＹ　・・・
4　Ａ社　　　　　　ＸＸＸ　・・・
5　Ｃ社　　　　　　ＲＲＲ　・・・
6　・・

と言った形で仕入れ先業者と取扱い商品名を登録して頂く形で
シート「仕入明細」
　　　　Ａ　　　　　Ｂ　　　　Ｃ　　　　　Ｄ
1　仕入れ先　商品名　単価　　数量　・・・
2
3

Ａ列に「仕入れ先」の業者を選択するとＢ列の入力が仕入れ先
業者が取扱いしている商品から選択して入力出来るようにする
為にはどの様な方法がありますでしょうか。

例えばＡ列の仕入れ先にＡ社を選ぶと　Ｂ列の商品名は仕入れ
マスターに登録されたＡ社取扱い商品のＺＺＺとＸＸＸから
選択して入力出来るようにする。

回答No.4

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

ほかの回答のことをあまり言いたくないのですが、
KURUMITOさんの2回目の回答(#3)においてINDIRECTは避けるべきです

INDIRECTを使って、17列*100行 作成してください
関係のないセルにデータを入力するのにも再計算に時間がかかってしまいます。

INDIRECTは揮発性を持った関数といわれています
(セルに値を入力するたびに計算が走るNOW関数と同様です)

=IF(OR($A2="",COUNTIF(仕入れマスター!$F:$F,$A2&"/"&COLUMN(A1))=0),"",
INDEX(仕入れマスター!B:B,MATCH($A2&"/"&COLUMN(A1),仕入れマスター!$F:$F,0)))
と書き換えるだけでも、計算速度がまったく変わります。
(というか、1回目の回答ではINDEXを使っているのにちょっと不思議でした)

失礼しました m(_ _)m

お礼

ご回答ありがとうございました。
結果としてはＶＢＡで対応しましたが、参考になりました。

回答No.5

watabe007

マクロで入力規則 を操作してみました。
シート「仕入明細」のシートモジュールに貼り付けてお試しを
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
　　Dim Str As String

　　With Target
　　　　If .Row = 1 Then Exit Sub
　　　　If .Column > 2 Then Exit Sub
　　　　.Validation.Delete
　　　　Str = GetList(.Column)
　　　　If Str = "" Then Exit Sub
　　　　.Validation.Add Type:=xlValidateList, Formula1:=Str
　　End With
End Sub
Function GetList(Col As Long) As String
　　Dim myDic As Object
　　Dim myRang As Range
　　Dim c As Range
　　Dim d As Variant

　　Set myDic = CreateObject("Scripting.Dictionary")
　　With Worksheets("仕入れマスター")
　　　　Set myRang = .Range("A2", .Cells(Rows.Count, "A").End(xlUp))
　　End With
　　For Each c In myRang
　　　　If Col = 1 Then
　　　　　　myDic(c.Value) = Empty
　　　　ElseIf Col = 2 Then
　　　　　　If c.Value = ActiveCell.Offset(, -1).Value Then
　　　　　　　　myDic(c.Offset(, 1).Value) = Empty
　　　　　　End If
　　　　End If
　　Next
　　GetList = Join(myDic.keys, ",")
　　Set myDic = Nothing: Set myRang = Nothing
End Function

お礼

ご回答ありがとうございます。
同様な考えをしたのですが、ご回答の内容であれば
リストがString型の為255までとなり問題？
配列とかを使えば上手く行くのかもしれませんが・・。
このため、作業用のワークシートへ該当リストを抽出し
そこを可変長で「名前」→「定義」を作成
入力の規則とするようにしました。
「仕入明細」側も仕入先の変動に応じて抽出が動的に
起こるようにイベント処理しました。

回答No.3

KURUMITO

ごめんなさい。勘違いをしていました。
仕入マスターでのＦ列の作業列は生かして、仕入明細でも作業列を設けます。
例えばＪ2セルには次の式を入力したのちに右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。
=IF(OR($A2="",COUNTIF(仕入れマスター!$F:$F,$A2&"/"&COLUMN(A1))=0),"",INDIRECT("仕入れマスター!B"&MATCH($A2&"/"&COLUMN(A1),仕入れマスター!$F:$F,0)))
その上でＢ列を選択して入力規則を設定します。
リストで元の値には次の式を入力してＯＫすればよいでしょう。
=J1:Z1
これでＡ列にＡ社と入力すればＺＺＺやＸＸＸの選択ができるようになります。

お礼

ご回答ありがとうございました。
結局ＶＢＡでマスターから該当商品を抽出して作業用のワーク
シートへリスト化し、仕入先に対して変動する入力の規則を
作成いたしました。

回答No.2

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

「仕入れマスター」はA列(仕入れ先)を昇順/降順で並べ替えます
「仕入明細」の2行目セルにおいて[Ctrl]+[F3]名前の定義
名前 商品リスト
参照範囲
=INDEX(仕入れマスター!$B:$B,MATCH($A3,仕入れマスター!$A:$A,0)):
INDEX(仕入れマスター!$B:$B,MATCH($A3,仕入れマスター!$A:$A,0)+COUNTIF(仕入れマスター!$A:$A,$A3)-1)

仕入明細!B2セルにおいて入力規則 - リスト
=商品リスト

補足

ご回答ありがとうございます。
「仕入れマスター」については、仕入れ先でソーティングしない
状況で実現させる方法はありませんでしょうか。
ＶＢＡなどで、常に仕入れ先毎に並べ変わるようには出来ると
思うのですが、基本的には最後の行に随時追加登録して行く形
を想定しております。

回答No.1

KURUMITO

最もわかりやすい方法は作業列を作って対応することでしょう。
仕入マスターのシートで例えばＦ2セルに次の式を入力して下方にオートフィルドラッグします。
=IF(A2="","",A2&"/"&COUNTIF(A$2:A2,A2))
仕入明細のシートではＡ2セルに仕入れ業者を入力します。
Ｂ2セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。
=IF(COUNTIF(仕入明細!$F:$F,$A$2&"/"&ROW(A1))=0,"",INDEX(仕入明細!$A:$E,MATCH($A$2&"/"&ROW(A1),仕入明細!$F:$F,0),COLUMN(B1)))

補足

早速ご回答頂きありがとうございます。
私のやり方が悪いのか上手く動作しません。
また、関数の解読が出来ていない状況で申し訳ないのですが、
この式で商品名の入力が選択して入力できるのでしょうか？

希望としては、仕入明細の商品名（Ｂ列）の入力が仕入れ業者
に連動して取扱い商品群から選択入力出来るようにしたいのですが。