EXCEL シート間の参照について

質問

Sheet1に郵便番号、住所、氏名の項目を作り1pageとします。
Sheet2の住所録リストから各項目へ参照させます。
たとえばSheet2の住所録リストが3行めならSheet1のpageも3です。
Sheet2の住所録リストに空白行を1行挿入したとき、参照されるSheet1のpageは空白となり、次の行が次のpageに参照されるというふうに、Sheet1のpageとSheet2のリスト行が常に同じになるようにしたいのですが、どうしたらよいかおしえていただけないでしょうか？

回答No.4

noname#262398

関数を使わず、単に
=Sheet2!A1
で良いのでは？
そうすれば、Sheet2に行を挿入したときに勝手に参照が変わってくれます。
pageは
=ROW(Sheet2!A1)

回答No.3

ham_kamo

> =TEXTを使うと住所、氏名はOKなのですが郵便番号だけなぜか表示されず（←郵便番号に関数を使っているからでしょうか？）

すみません、この原因はちょっとわかりません。最後の""がまずいのかもしれませんが、どういう関数でどういう書式設定で郵便番号を表示させているのかわかれば、また調べることは可能です。が、

> 結局=INDEX(Sheet2!$A:$A,ROW($A$1))としてみました。

でうまくいくなら、それでいいと思います。
しかし、ROW($A$1)だとセルが絶対参照なので、下にコピーしてもずっと$A$1のままになってしまいます。ROW($A1)と行番号を相対参照にするか、ROW()だけでもいいと思います。

> Sheet2のリストデータにさらに空白の列を挿入するとして、Sheet1の参照が行同様に変化するようにするには

というのは、Sheet2に、たとえば住所と氏名の間に空白の列を挿入したとき、Sheet1も挿入した列は空白になり、氏名の欄が右に移動する、ということでしょうか？

それなら、
=TEXT(INDIRECT(ADDRESS(ROW(),COLUMN(),1,TRUE,"Sheet2")),"")
としてみればいかがでしょうか。

それとも、Sheet2に列が挿入されてもSheet1の列構成が変わらないのであれば、No.2の回答のとおり、
=TEXT(INDEX(Sheet2!B:B,ROW(),1),"") （Ｂ列の場合）
としておけば、列が相対参照なので、自動的に参照先が変わります。

もし違っていたらまた補足をお願いします。

回答No.2

ham_kamo

私も質問の意図を正しく理解できてるかちょっと不安なのですが、pageと書かれているのは、要はSheet1の行のことですよね？

No.1さんの回答どおりやってみたところ、実際にSheet2に空行を挿入したらSheet1には空行でなく0が入ってしまいました。
ちょっと手を加えて、

A1: =TEXT(INDIRECT("Sheet2!A"&ROW()),"")

としてみればどうでしょうか。

＜別解＞
=TEXT(INDEX(Sheet2!A:A,ROW(),1),"")
=TEXT(OFFSET(Sheet2!$A$1,ROW()-1,0,1,1),"")

など。なお、Sheet2で郵便番号がB列など、Sheet1と列構成が異なる場合、上記の式の中のAはすべてBに置き換えてください。他の項目も同じです。

補足

ありがとうございます。
=TEXTを使うと住所、氏名はOKなのですが郵便番号だけなぜか表示されず（←郵便番号に関数を使っているからでしょうか？）
結局=INDEX(Sheet2!$A:$A,ROW($A$1))としてみました。
なんとなくうまくいきますが、これではどこかで不具合がでてくるでしょうか？
ちなみにSheet2のリストデータにさらに空白の列を挿入するとして、Sheet1の参照が行同様に変化するようにするにはどうしたらよいかも併せておしえていただけないでしょうか？
図々しく申し訳ありません。

回答No.1

onntao

ご希望の内容がつかみづらいのですが
Sheet1A1：=INDIRECT("Sheet2!A"&ROW())
以下にコピーかな
蛇足ながら、A1のROW()で1を利用していますので、
ご自身のデータ行位置によっては、改変が必要な場合がありますね
（Sheet2では5行目なのに、Sheet1では10行目から利用するならROW()+5とか）

お礼

ありがとうございます。