Excelでリスト（データの入力規則など）使用時

項目が増えたり・減ったり・変わったりした時に、データの入力規則をいじって・・リストに反映して・・・ということや。

同じシートの欄外にリスト用のエリアを設けていたら、いつの間にか壊れていた・・・なんてことはないでしょうか。

或いは、それを回避するためにマスタシートを別に設けてみるも、項目の入れ替わりが多いと管理が面倒になったりなど。

ということで、関数のみで手っ取り早く、リストの管理から開放されましょう。

概要

マスタシートを設けます。
マスタシートに、それぞれマスタとなる項目を入力します。
項目は自由に増やしたり減らしたり変更することができます。
参照元（リストとなるセル）は何もしなくても選択肢が動的に反映されます！
※項目の変更については、既に入力済みのセルへの反映は行われません（あくまで選択肢のみ）

手元にExcelがなかったので、GoogleSpredSheetで書いていますが、Excelで実現してください。
※GoogleSpredSheetでは同じことができません。

見た目はこのような感じで、マスタ用のシートを設けます。
器として、列は20列、行は50列用意しています。

マスタシートの初期設定を説明します。

セル	項目	値	内容
B1	sheet	mst	シート名を入力します。
B2	開始行no.	5	マスタの値を入力し始める行番号です。例では天体観測が入力されている行のことです。

これらは、マスタシートのシート名や、開始行が変更された際に手で変更します。

=LEFT(ADDRESS(ROW(),COLUMN(),2), FIND("$",ADDRESS(ROW(),COLUMN(),2))-1)

関数のあるセルの行絶対参照（e.g. A$1）の値から、"＄"までの文字列を抜き出す（つまり列名称）。

=COUNTA(D5:D54)

マスタの50項目までの行数をカウントします。

=CONCATENATE($B$1,"!",D2,$B$2,":",D2,$B$2+D3)

初期設定値とマスタの入力情報を元に、マスタ項目の範囲を作成します。

D列以降の1行目は、マスタの名称を入力します。これはただの見出しです。
そのマスタに対し、同列の5行目からリストの選択肢となる値を入力していきます。

これで、動的に反映されるリスト指定が完了です。
4.にて、INDIRECT関数を使用することで、対象セルの文字列をセル参照値として扱うので、結果マスタの項目を指定することになります。

例えば、mstシートの好きな歌列に、「カルマ」を追加します。
そうすると、入力規則を設定したセルの選択肢に、「カルマ」が追加されます。

以上、画像で例示できないのが残念ですが・・。
シートは置いておくので、xlsx形式でダウンロードして確認してみてください。