【Excel 入力規則】名前を定義したセルをINDIRECTでプルダウン表示させるとき、定義していない場合も処理する

Question

お世話になります。
使用ソフトはWindows版 Excel 2016です。

入力規則にリストを使用し、INDIRECTを使用した数式を参照させ、
事前に名前を定義しておいたセル範囲をプルダウンで表示させる処理を考えています。
ただし、INDIRECTの参照元が無数にあり、事前にすべてに対応したリストを用意することができません。

そこで、ISERRORやIF等を組み合わせて、名前を定義している場合は定義したリストを表示し、
そうでないその他の場合は、その他のために用意した汎用のリストを表示させたいです。

以下、セルの例とエラーが出ている入力規則の数式を示します。

INDIRECTの参照セル(A1): トマト, きゅうり, キャベツなど (無数に可能性があります)

名前を定義するセル範囲

トマト	きゅうり	きゃべつ	汎用
愛知	福岡	北海道	東京
三重	長崎	青森	埼玉
静岡	宮崎	秋田	神奈川

実際は表ではなく普通にセルです (例. E1=トマト, E2=愛知, F1＝きゅうり)。
上表の「カラム名+"産地"」を名前とし、都道府県をセル範囲にして定義しています。

入力規則を設定しプルダウ表示させるセル (B1)
以下は入力規則の数式で試したもの

=IF(ISERROR(INDIRECT(A1&"産地")),INDIRECT("汎用"),INDIRECT(A1&"産地"))
=IFERROR(INDIRECT(A1&"産地"),INDIRECT("汎用"))

以上の数式では、名前に定義したセル範囲と名前に定義していない汎用リストのどちらも
プルダウンで表示することができませんでした。

やりたいこととしては、
A1にトマト、きゅうりが入力された場合はそれぞれのリストをプルダウン表示し、
それ以外の野菜の場合は汎用リストをプルダウン表示する。

お力をください。
ご不明点やご質問がございましたらお答えします。
宜しくお願いします。

Answer 1

質問にある「名前を定義するセル範囲」のタイトル部分を利用するのはどうでしょう。
「名前を定義するセル範囲」のタイトル(トマト、きゅうり、きゃべつ）が(E1:G1)とすると,入力規則は以下の式になります。

=INDIRECT(IF(ISERROR(MATCH(A1,E1:G1,0)), "汎用", A1 & "産地"))

※回答には関係ありませんが、A1自体が上記タイトルを元にした入力規則でも良い気がします。

Comments

[s-o]

仮環境で望む通りに処理が行えました。
MATCHで一致するかどうか確認し、その結果をISERRORで拾って、
IFで処理分けした単語でINDIRECTするということですね。勉強になりました。
実際のA1は自動入力されるものでして、上記質問となりました。
明日、本番環境で確認します。本当にありがとうございます。

7/30 本番環境でも思い通りに動作しました。ありがとうございました。