[[20250627111012]]　『indirectがうまくいきません』（老人）　ページの最後に飛ぶ

『indirectがうまくいきません』（老人）

以下のような表を使って、２段階でリストを連動しようとしていますが
上手く連動できません。どこがいけないのでしょうか？

印刷シート

 　#　　A　　#　　B    #
 １　地名　　　　おみやげ
 ２　東京　　　東京バナナ

dataシート

 　#　　A　　#　　　　B　　　#　　C　　　　　#　　D　　　　　#
 １　 地名　　　東京　　　　　　　大阪　　　　　　福岡
 ２　東京　　　東京バナナ　　　たこやき　　　　梅ケ枝餅
 ３　大阪　　　草加せんべい　　豚まん　　　　　あまおうチョコ
 ４　福岡　　　グレイシア　　　みたらし小餅　　鶴乃子

dataシートのデータは増減が予想されるため、
行の範囲指定を可変にしたいです。

印刷シートのA2セル以下には、データの入力規則として
以下の式が入っています。

=OFFSET(data!A2,,,COUNTA(data!A:A)-1)

これでdataシートから地名を読み込むこと。
地名データの増減は対応できました。

また、名前の定義で、以下のようにしてあります。

東京
OFFSET(data!$B$2,,,COUNTA(data!$B:$B)-1)
大阪
OFFSET(data!$C$2,,,COUNTA(data!$C:$C)-1)
福岡
OFFSET(data!$D$2,,,COUNTA(data!$D:$D)-1)

Ｂ２セルに

=indirect(A2)

といれたところ、まったくＢ２セルが反応しません。
どこが間違っているのでしょうか？
よろしくお願いいたします。

※データの範囲をそれぞれ直接セルの番地で定義した場合は
　リストが表示されます。

追記
マクロについては、使用禁止になっており使えません。

＜使用 Excel：Microsoft365、使用 OS：Windows11 ＞

テーブルとFILTER関数でどうでしょう。

 Dataシートの表は以下のようにしてテーブル化（名前はテーブル1とします）

    |[A]         |[B]         |[C]           
 [1]|東京        |大阪        |福岡          
 [2]|東京バナナ  |たこやき    |梅ケ枝餅      
 [3]|草加せんべい|豚まん      |あまおうチョコ
 [4]|グレイシア  |みたらし小餅|鶴乃子

 印刷シート

 A2 データの入力規則　=$D$2#
 B2 データの入力規則　=$E$2#
 D1 =TRANSPOSE(テーブル1[#見出し])
 E2 =FILTER(テーブル1,COUNTIF(A2,テーブル1[#見出し]),"")

    |[A] |[B]     |[C]|[D]    |[E]         
 [1]|地名|おみやげ|   |作業列1|作業列2     
 [2]|大阪|豚まん  |   |東京   |たこやき    
 [3]|    |        |   |大阪   |豚まん      
 [4]|    |        |   |福岡   |みたらし小餅
（ななし） 2025/06/27(金) 13:37:16

ありがとうございます。
思っていた内容が実現しました。

マクロが大嫌いな責任者がいるため、マクロを使わずに実現する必要があったので助かりました。
（老人） 2025/06/27(金) 15:58:26

 解決したようですね。
 B2セルに
 =OFFSET(data!$A$2,0,
MATCH(A2,data!$B$1:$D$1,0),
COUNTA(OFFSET(data!$A$2,0,MATCH(A2,data!$B$1:$D$1,0), 20)))
と参照するセル範囲を指定すればよさそうです。
 名前の定義が正しくできていない感じです。

（Hatch） 2025/06/27(金) 16:04:19

 365を使っているなら、こんな方法も。
●最新の365
=TRIMRANGE(XLOOKUP(A2,data!$1:$1,data!$2:$100))
●少し古い365
=LET(a,XLOOKUP(A2,data!$1:$1,data!$2:$100),TAKE(a,XMATCH("?*",a,2,-1)))
（んなっと） 2025/06/28(土) 07:10:19

 2番目の式、数値も混ざるときは
=LET(a,XLOOKUP(A2,data!$1:$1,data!$2:$100),TAKE(a,XMATCH(TRUE,a<>"",,-1)))
（んなっと） 2025/06/28(土) 08:06:52

名前の定義ですね。見直してみます。

んなっと様もありがとうございました。
（老人） 2025/06/30(月) 12:05:30

 おそらくですが、名前の定義の内容が関数の場合に、INDIRECT関数でエラーになると思われます。

 なので、回答者の皆さんは、B2に入れる式を提案してくれているのだと思います
（´・ω・`） 2025/06/30(月) 12:33:07

 東京
 =OFFSET(data!$B$2,,,COUNTA(data!$B:$B)-1)
 などと名前定義されているのであれば、

 =IFS(A2="東京",東京,A2="大阪",大阪,A2="福岡",福岡)
 とB2セルの入力規則(リスト）の式にセットするという
 初歩的方法ではダメですか？

（xyz） 2025/06/30(月) 21:20:14

 質問者さんの質問にあるそもそもの事例に関して、ちょっと調べてきました。

 この問題は海外のQ&Aサイトでも取り上げられ色々と議論はされていますが、
 結論としては、
入力規則のドロップダウン表示に関して、INDIRECTを使って「OFFSETを使った式を名前定義したもの」を利用することはできない
 ということのようです。（´・ω・`さんが指摘されているとおりだと思います）
 （動的な名前付き範囲への間接呼び出しは実際上不可能で、バグという指摘もあるが、
   MS社は何も言明していないので、"いわゆる仕様"ということになるのだと思います。）

 【ご参考】（読む価値は乏しいと思いますが、あくまで資料価値のためだけです）
 (1)2017/11/8 質問
https://answers.microsoft.com/en-us/msoffice/forum/all/excel-dynamic-name-using-offset-not-working/9f5642f8-1355-4471-9760-a61f353bc978?utm_source=chatgpt.com
 (2)2019/9/19 質問
https://techcommunity.microsoft.com/discussions/excelgeneral/dynamic-data-validation-indirect-offset-anyone-can-help/862357
 (3)
 ↓はMS社ではない別のフォーラムです。
https://chandoo.org/forum/threads/indirect-using-dynamic-named-range.12282/
 これも解決はしていません。
 最後のコメント(May 17, 2015)が解決しているように見えて、実はマクロによる対応でした。

 （ななし)さんや（んなっと）さんから既に指摘がありますように、
 Exce365であればOFFSETを使わずに範囲指定ができるので、
 それを使うのがよいと思います。

 # なお、生成AIに尋ねてもこうした知識は持ち合わせていないようで、色々と提案はしてきますが、
 # 殆どはピントを外した確認事項でした。

（xyz） 2025/07/02(水) 12:43:06

コメント返信：

[ 一覧(最新更新順) ]

[[20250627111012]] 『indirectがうまくいきません』（老人） ページの最後に飛ぶ

[[20250627111012]]　『indirectがうまくいきません』（老人）　ページの最後に飛ぶ