シート名を取得して、隣のシートを参照する

質問

excel2007での、隣のシートを参照する方法を試しています。
例えばh23-3のシートのAL3とh23-4のシートのAH3の加算結果を
h23-4のシートのAL3に格納したいのですが、循環しているとエラーが出てしまいます。
分かる方いらっしゃいましたら、ご教授ください。
シートの名前は、h23-4 h23-5のように連続性のある名前にしてあります。
h23-4のシートのAL3に以下の内容を記述しました。

=AH3+INDIRECT("'"&MID(CELL("filename"),FIND("]",CELL("filename"))+1,4)&RIGHT(CELL("filename"))-1&"'!AL3")

よろしくお願いします。
自力で作ったため、効率悪いかもしれません。
また、他に良い方法があれば教えてください。

回答No.1

keithin · ベストアンサー

厳密に言うとその式は循環参照していませんが，cell関数の内部計算で，特定の操作によってそういう結果が表示されます。エクセルのバグと考えて構わないです。
簡易には数式を今のままF2しEnterし直すと，目がさめたようにふつーに計算してくれます。

安全策としては，今の式中で３箇所使用している
CELL("filename")
という記述を，
CELL("filename",A1)
に漏れなく直してください。

＃参考
=AH3+INDIRECT("'"&TEXT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,6)&"-1")-1,"ge-m")&"'!AL3")
といった式にしておくことで，h24-1シートからh23-12シートも問題なく参照できるようにできます。

お礼

ありがとうございます！
エラー無くできました！エラーメッセージ分かりにくいですね・・・循環じゃないだなんて・・・

参考もありがとうございます！h23-12⇒h24-1のところは、IF関数でクリアするつもりだったんですが、そのような方法もあったんですね。
TEXT関数は盲点でした。ありがとうございました！

回答No.2

imogasi

（１）シート名
出来ればシート名が関数で取れれば良いのだが、CELL関数という特殊な関数を使い、かつ式が長くなってしまう。其れでユーザー関数で
標準モジュールに
Function shn()
shn = ActiveSheet.Name
End Function
ーー
を作る。セルで=shn()と入れるとシート名が出てくる。文字列として関数式の中でも使える。
ただしフルパスではなくファイル名の部分だけが出る。
例　=MID(shn(),1,3)
ーーー
（２）次のシート名
ここでいう「次」とはシートタブが右隣にあるもののシート名ではなく、
文字列＋数字で数字が連続して増えていく場合を考える。
ただし１０シート以上になると、下記では繰り上がりを考慮してないので、プログラムを修正する必要がある(略）
標準モジュールに
Function shna()
shnm = ActiveSheet.Name
v = Val(Right(shnm, 1)) + 1
'MsgBox "*" & Str(v) & " * """
shna = Left(shnm, Len(shnm) - 1) & Trim(Str(v))
End Function
--
セルに=shna()
これらをINDIRECT関数のシート名のところへ使ってはどうでしょう。

お礼

ありがとうございます！
VBAも考えてはいたんですが、私には、少し難しく感じました。
しかし、皆さん知識がおありで羨ましい限りです
大変参考になりました。