エクセル名前の管理

質問

エクセルの名前の管理について質問です。

Aのシートでデータの入力規則に従い、別シート（B)で作成したリストを指定してプルダウンメニューで選択できるようにしています。
Bのシートでは列単位でリストを作成しています。
リストを追加すると自動でプルダウンメニューに追加するには、COUNTA関数を使って、入力の個数を戻り値としてとらえればできるのはわかったのですが、

名前の管理の参照範囲を
=OFFSET(B!$R$3,0,0,COUNTA(B!$R:$R)-1,1)
とすると上手くいくのですが、

COUNTAの範囲を「R列」全てではなく、R3～R100までとした時
=OFFSET(リスト!$R$3,0,0,COUNTA(リスト!R$3:R$100),1)
なぜかエラーになってしまいます。
R２にデータが入っておりこれはカウントさせたくない為、上では戻り値-1させています。
これをR3～R100までのカウントにして-1を省いてみました。

セルに
=COUNTA(リスト!R$3:R$100）
として、R列のデータ入力されている個数を表示させるときちんと出るのですが、上の構文だとおかしいのでしょうか？

また、気になるのは、名前の管理を保存して閉じ、再度変更した箇所を開くと
=OFFSET(リスト!$R$3,0,0,COUNTA(リスト!XF$3:XF$100),1)
と指定範囲が勝手に変わっています。

名前の管理の参照範囲が勝手に変わることなどあるのでしょうか？
バグなのか、関数の指定がおかしいのかがわかりません。

どこが悪いのか、またどのようにすればいいのかわかる方ご教授お願い致します。
使用しているのはMSオフィスのエクセル2007です。

回答No.2

keithin · ベストアンサー

＞=OFFSET(リスト!$R$3,0,0,COUNTA(リスト!R$3:R$100),1)

名前の定義の中身に数式を設定する際は，基本，必ず
=OFFSET(リスト!$R$3,0,0,COUNTA(リスト!$R$3:$R$100),1)
のように「絶対参照で」作成します。

そうしないと，「名前の定義を登録した瞬間のアクティブセルからの相対参照」で名前定義が作成され，結果して「名前定義を呼び出したセルからの相対参照」で計算されてしまい，固定のＲ列を計算してくれません。
（そういう動作をするという理解の上で相対参照で名前定義を利用する事も可能は可能ですが，今回ご質問の内容ではそういう使い方ではありません）

お礼

なるほど。

名前の定義の作成をいまいち理解できておりませんでした。

ご指摘どうり、R列を絶対参照することで、問題なく動作致しました。

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

回答No.1

noname#203218

2003の入力規則の説明サイトにあった式です。私も2007ですが、問題なく実行出来ました。ご参考まで。
=OFFSET(Sheet2!$A$3,0,0,SUM((LEN(Sheet2!$A$3:$A$100)<>0)*1))

お礼

こちらでも確認しましたが、おっしゃられる方法でも求めている動作を致しました。

調べて頂きありがとうございます。

ただ、回答頂いたのに誠に申し訳ないですが、今回は関数の指摘を頂いた、No2の方をベストアンサーとさせて頂きます。

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