エクセルで別シートのセル内容を参照したい(再）

質問

先日ここでご回答頂いた回答に関する質問です。

B3に入っている文字列のシート名のD34を参照する回答で下記の式を教えて頂いて、直ぐに試してみたらうまくいったのですが、今日朝一で試しにB3の文字列（別シート名）を入れてみたら変更前のシート名のセル番地のままでエラーになってしまいました。
=IF(B3="","",INDIRECT(B3&"!D34"))
つまりB3の文字列（シート名）に対応した動きにならないことが分かりました。
式で何か抜けているのでしょうか？

回答No.1

kkkkkm · ベストアンサー

別シート名の入力間違いじゃないですか

補足

それがシート名は教えて頂いたVBAで自動でリスト化されるし、実際に間違ってはいません。

つまりシート名は=INDiRECTで変更されますがその後のセル番地が変わらない、と言うことだと思います。

確認のためにA3セルから下に１，２，３，４と入力し、シート名を１，２，３，４にしたエクセルの目次シートのA3から
=IF(A3="","",INDIRECT(A3&"!D3"))
=IF(A4="","",INDIRECT(A4&"!D4"))
=IF(A5="","",INDIRECT(A5&"!D5"))
=IF(A6="","",INDIRECT(A6&"!D6"))
と入力して、A3セルを１から４に変更すると、
シート４のD3を参照します。
意図はシート４ならD6を参照したいのです。

回答No.8

kkkkkm

> 書式のパターンは決まっておらず担当者が情報量によって行を挿入したりしているのが実態です。

必ず最終行を取得とかでしたら以下のサイトの方法で行が指定できますが

【詳細解説】エクセル関数で最終行の行番号・内容を取得する
https://www.excelspeedup.com/saisyuugyou/

中途に行を挿入したデータを参照するとなるとそれは無理っぽいのでakira0723さんの選択した方法(書式の統一)が最善策だと思います。

お礼

今回はお手数をお掛けして申し訳ありませんでした。既存のエクセルの定型書式へのコピペに着手しました。 各担当者が作成したエクセルを保存していたのでその情報をリスト化して活用しようとして発生した問題でした。

Powered by GRATICA

回答No.7

kkkkkm

おまけです
たとえば回答No.1の補足にある状態の表のパターンなら

=IF(A3="","",INDIRECT(A3&"!D" & A3+2))
として6行目までコピーすれば
A3に入力したの数値によって該当するシートの指定したセルを参照させられますが
(A3が1ならD3、4ならD6のように)

そんな単純なパターンじゃないと思いますが、パターンが決まっていれば数式だけでもできる可能性はありますという事で。

補足

何度ものご回答感謝！感謝！！

書式のパターンは決まっておらず担当者が情報量によって行を挿入したりしているのが実態です。
で色々試してみた結果、例えば別表に対応するセル番地を入れる方式ではその表をメンテする必要が出てきたり、で結局現状のセルの結合や行の追加を許容する限りは今後継続的に手間がかかることになってしまうことになりそうです。

そこで諸悪の根源である書式を統一することにしました。
現在は30枚程度のエクセルなのでこれを定型書式に入力し直し、担当者には今後は定型書式への入力を指示することにします。

よって最初のご回答のINDIRECT関数で対応できるようにします。

本当にお手数をおかけしてしまい申し訳ありませんでした。

回答No.6

kkkkkm

回答No.5のような参照先のセルの行番号を連番にするというだけの単純な話なら

回答No.1の補足

> =IF(A3="","",INDIRECT(A3&"!D3"))
> =IF(A4="","",INDIRECT(A4&"!D4"))
> =IF(A5="","",INDIRECT(A5&"!D5"))
> =IF(A6="","",INDIRECT(A6&"!D6"))
> と入力して、A3セルを１から４に変更すると、
> シート４のD3を参照します。
> 意図はシート４ならD6を参照したいのです。

説明にある意図はどうなってるのだということになりますね。
(意図を無視したらNo.5の方法はすぐに思いつく方法ですが)

元の式(目次シートのA3から入力は間違いとして)
=IF(A3="","",INDIRECT(A3&"!D3"))
が
=IF(A3="","",INDIRECT(A3&"!D6"))
に結果的に変化するような事をしたいのだと思いますので
(違うのでしたら意図の説明が無茶苦茶です)

回答No.5だとA3セルを１から４に変更してもD6を参照しません

シートによって参照先が変化して、シートと参照先先セルになんらかのパターンがないのでしたら
回答No.3の方法しかないと思いますよ。

回答No.5

SI299792

B3: =IF(A3="","",INDIRECT(A3&"!D3"))
B4: =IF(A4="","",INDIRECT(A4&"!D4"))
B5: =IF(A5="","",INDIRECT(A5&"!D5"))
B6: =IF(A6="","",INDIRECT(A6&"!D6"))
という風に変化させたいのであれば、

B3: =IF(A3="","",INDIRECT(A3&"!D"&ROW()))
下へコピペ。

お礼

お礼が抜けておりました。 現状は担当者が情報量により行を挿入したりセルを結合したりしているので定型化することは無理です。 今後は定型書式を使うようにPRすることにしました。

Powered by GRATICA

回答No.4

kkkkkm

> シートの対応したセル番地の内容の参照

どのシートでどのセルを参照するのかこちらではわかりません
と回答しましたが見えますか。

> 現状のシートで動作確認し見事に一致した

現状のシートとか言われてもこちらで現状のシートがどのようなものかわかりません。

> シートを有る順番の処に挿入したらそれ以降のシートでエラーになって

どのようなエラーですか。
シートを挿入してもエラーにはなりません。

回答No.3

kkkkkm

添付画像のような参照セル一覧表を作成して(表内のシート名とセル番地は適当に記載しています)

C3に
=IF($B3="","",INDIRECT($B3&"!" & VLOOKUP($B3,$J$3:$M$10,COLUMN(B1),FALSE)))

($J$3:$M$10は実際の一覧表の範囲を指定)

として右と下にコピーすれば指定したセル位置を参照できます。

回答No.2

kkkkkm

> エラーになってしまいました

エラーになったのですか？

> つまりシート名は=INDiRECTで変更されますがその後のセル番地が変わらない、と言うことだと思います。

そういう式ですので当然です。

> 意図はシート４ならD6を参照したいのです。

そんな意図を示しましたか？

> 直ぐに試してみたらうまくいった

上記の意図であれば試してうまくいくはずはないですよ。

やりたいことは添付図でとかの説明でD34,F3,H12しか指定してなくて他のシートは違うとか言われても
どのシートでどのセルを参照するのかこちらではわかりません。

補足

質問の文章が不適切でした。
やりたいことはVVBAで取得したシートの対応したセル番地の内容の参照です。

最初にうまくいったのは現状のシートで動作確認し見事に一致したので「うまくいった」ように見えたのですが今日早速シートを有る順番の処に挿入したらそれ以降のシートでエラーになって不具合に気付いた次第です。
無駄なお手数をお掛けしてしまいましたが、INDIRECT関数で目的の動作に出来るのでしょうか？