エクセル2007 OFFSET関数 INDIRECT関数

質問

エクセル2007でOFFSET関数とINDIRECT関数を用いてセルの入力規則をして、リスト表示したいと思っています。
入力規則のリストは動的な値を設定したいと思っています。

　ＡＢＣＤＥ
１あいうえお
２かきくけこ
３さしすせそ
４たちつてと
５なにぬねの

というシートA列に”データベース1”B列に”データベース2”・・・といった具合に名前を定義します。このデータベースは行がどんどん増えていく可能性があるため、名前の定義の参照範囲欄に
(1)=OFFSET($A$1,0,0,COUNTA(A：A),1)
　としました。

別シートに
表示したいセルで入力規則→設定タブ→入力値の種類→リストを選択。
元の値の欄に
(2)=INDIRECT(A2&"1",FALSE)
　としました。
　※A2は”データベース”と入力してあるセルです。

ここからがわからないのですが
上記式(1)、(2)の両方とも単独で使用した場合は欲しい値が得られるのですが、組み合わせて使用した場合はリストが出てこなくなってしまいます。

1)組み合わせて使うことはできないのですか

2)ほかにいい方法はありますか

ということを質問します。
よろしくお願いします。

回答No.2

mt2008

確認して見ました。
確かに、OFFSETを使った名前定義は、入力規則の参照先をINDIRECTで定義できませんね。

INDIRECTはそのままにして、名前定義の範囲設定をVBAでやらせてはいかがですか。
リストがあるシートの　Worksheet_Change　に、↓でいけると思います。
ActiveWorkbook.Names.Add Name:="データベース1", RefersToR1C1:="=R2C1:R" & Range("A65536").End(xlUp).Row & "C1"

回答No.1

mt2008

元データの行数が増えたら、入力規則のリストもそれに合わせて増えるようにしたいと言う事で良いのでしょうか？
単純に、元データのA列全体を　データベース1　と名前定義して、別シートの入力規則の「元の値」欄に「=データベース1」とするだけで良さそうに思えますが、それでは駄目ですか？

お礼

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

>A列全体を　データベース1　として名前定義して、・・・・

そのように名前定義すると、
1)入力規則を設定したセルのリストが選択した分だけ長いリストになってしまいます。
2)入力規則を設定したセルでリスト表示にすると、最初に表示されるリスト欄が空白の部分を表示してしまって非常に不便です。
3)1行目にそれぞれの列に対して名前がついてるのでそれをリストに表示したくない(質問に書いていませんでした。ごめんなさい。)。

　ＡＢＣＤＥ
１D1D2D3D4D5
２あいうえお
３かきくけこ
４さしすせそ
５たちつてと
６なにぬねの
(1行目は　データベース1・・のような名前)

回答者様のmt2008さんのやり方でもできるのですが、スマートではないような気がしています(申し訳ありません)。
よろしくお願いします。