連動させたドロップダウンリストが前の値を反映しない

質問

こんにちは。
先日質問して、一応解決したと思っていたんですが、なぜか機能しなくなりました。
以下がその例です。
はじめはA列の値を受けて、B列の値がしぼられていたのですが、現在は、A列が空欄でもB列のリスト選択が出来てしまいます。C列も同様です。
なにが原因かいろいろ値を変えたり、入力し直したりしましたが一向に改善の兆しが見えません。
アドバイスお願い致します。

シート2に
　　　Ａ　　　Ｂ　　　　Ｃ
１　式場　　|Ａ式場　　|Ｂ式場
２　会場１　|ａａ　　　|ｄｄ
３　会場２　|ｂｂ　　　|ｅｅ
４　会場３　|ｃｃ　　　|
５　担当１　|ａさん　　|ｄさん
６　担当２　|ｂさん　　|ｅさん
７　担当３　|ｃさん　　|

シート１の２行目に入力規則を適用
　　　Ａ　　　Ｂ　　　　Ｃ
１　式場　　|会場　　　|担当
２　規則１　|規則２　　|規則３

名前を次のように定義して（各ブロックを選択）
式場：=Sheet2!$B$1:$C$1 (実際はZまで広げています)
会場：=OFFSET(Sheet2!$B$1,1,MATCH(Sheet1!$A$2,Sheet2!$B$1:$C$1,0)-1,3,1)
担当：=OFFSET(Sheet2!$B$1,4,MATCH(Sheet1!$A$2,Sheet2!$B$1:$C$1,0)-1,3,1)

入力規則に次のように定義。
リスト　規則１=式場
リスト　規則２=会場
リスト　規則３=担当

回答No.3

namnam6838 · ベストアンサー

式場名を列記し、これに「式場」という「名前」をつけます。
付け方は、範囲選択をし、左上のA1とか表示されているところをクリックして文字を入れるだけです。
ex)名前「式場」の範囲のセルに、A式場・B式場が列記されているとします。

会場名を列記し、これに「A式場」「B式場」という名前をつけます。
ex)AA,BB,CCを列記しこれにA式場、DD,EEを列記しこれにB式場という名前をつけます。

担当者を列記し、これに「AA」「BB」・・という名前をつけます。

あるセル（たとえばA1）を選択し、入力規則で、リストを選び、元の値に、「=式場」と入れます。
=を入れるのを忘れないでください。
すると、名前「式場」に含まれているA式場・B式場がリストで選べるようになります。

隣りのセル（B1）を選択し、入力規則でリストを選び、元の値に、「=INDIRECT(A1)」と入れます。
すると、A1で選ばれている名前に含まれている会場が選べるようになります。
A1が空白のときは選択肢がなく選べません。
設定するときA1が空白の場合は「エラーだけど続けますか？」と行って来ますが「はい」とします。

隣りのセル（C1）を選択し、入力規則でリストを選び、元の値に、「=INDIRECT(B1)」と入れます。
B1で選ばれている名前（会場名）に含まれている担当者が選べるようになります。

ひとつつくったら、あとはA1～C1をずらずらとコピーしていけば同じ入力規則の欄ができます。

式場・会場・担当者を列記するところは、同じシートでも別のシートでもかまいません。

お礼

回答ありがとうございます。

素人の私にもとてもわかりやすい内容で助かります。
一つの作業でもいろいろな方法があるのですね。本当に勉強になります。

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

補足

すいません、回答を頂く前に自己解決しました。

＞名前を次のように定義して（各ブロックを選択）
＞式場：=Sheet2!$B$1:$C$1 (実際はZまで広げています)
＞会場：=OFFSET(Sheet2!$B$1,1,MATCH(Sheet1!$A$2,Sheet2!$B$1:$C$1,0)-1,3,1)
＞担当：=OFFSET(Sheet2!$B$1,4,MATCH(Sheet1!$A$2,Sheet2!$B$1:$C$1,0)-1,3,1)

の「$A$2」を「$A2」とすることで、正常に機能しました。

回答No.2

namnam6838

前回の質問と期待される結果がよくわからないのでなんともいえないのですが、
絞っていくのでしたら、入力規則とindirect関数の組み合わせが簡単だと思いますが、どうでしょうか。

参考URL：

http://homepage1.nifty.com/kenzo30/ex_kisopoint/onepoint_sonota3.htm

お礼

回答ありがとうございます。
A2で式場名をリストから選んだら、B2でその式場の会場名を、C2で式場に在籍している担当者を選択できるようにしたいのです。

＞入力規則とindirect関数の組み合わせが簡単だと思いますが、どうでしょうか。
これは2つのリストまでしか適用できないのでしょうか？
素人なもので、私の例に当てはめる方法がわかりませんでした。

補足

なぜか、一行目だけは正常に機能しています。

回答No.1

mshr1962

式場がブランクの検索列をB列にすればいいのでは？
（B2に式場を選んでください等のメッセージをいれてもいいと思います。）

補足

B列、C列ともにすべてのセルがA2の値を参照しているようです。隣にあるA列の値を参照してくれるには他に方法はないのでしょうか？