エクセルでのシート転記について

質問

現在、会社の倉庫住所の一覧表をエクセルで作っております。
SHEET1には各倉庫の住所を記載しています。
倉庫名、県、市、町、番地をそれぞれ列ごとに分けて一行に一つの倉庫の住所が納まるように入力しています。
例：A1に倉庫名、B1に県、C１に市、D１に町村、E1に番地、F1に電話番号。。。といった具合です。

そしてSHEET2には住所打ち出し用のフォーマットがあります。
A10に倉庫名（A10='SHEET1'!A1というふうに)、A11に県、A12に市、という具合に、セルごとに住所がそのまま入るようになっております。

例えばSHEET1/A1の倉庫名が入力されたセルをアクティブにすると、SHEET2に1行目の倉庫住所が、A2を選択する二行目の住所が反映する、というようなVBAを組むことができるでしょうか？

最初は単に住所をコピーしてシートを沢山作ろうと思っておりましたが、倉庫が30近くあるので、なんとか一覧の住所を利用して出来ないかと。。。

リストとVLOOKUP,IFを組み合わせた関数で対処してみましたが（SHEET2に倉庫名を選択するセルを作成・その値によってSHEET1から引用する内容を変える）出来れば、操作そのものはSHEET1のシートだけで完結させたいと思っております。

OS WINDOWS VISTA
バージョン　エクセル2003を使用していますが、ファイルそのものはエクセル2000、2002の人も使う可能性が高いです。

拙い説明で申し訳ございません。
どなたか、ご教示下さいませ。

回答No.2

KURUMITO · ベストアンサー

マクロを使うというよりもシート2のＡ列で倉庫の名前を選択できるようにして、選んだ名前の倉庫のシート1のデータが表示できるようにすればよいでしょう。
どちらのシートでも1行目は項目名があり、2行目から各データがあるとします。
初めにシート2でＡ2セルから例えばＡ100セルまでを選択し、「データ」メニューから「入力規則」をクリックします。
設定のタブで入力値の種類の窓では「リスト」を選択し、元の値の窓には Sheet1!A$2:A$40 と入力してＯＫします。
これでＡ列では倉庫の名前を選択することができます。
次にその選択された倉庫についてのデータをシート1から引き出し表示させるわけですが、Ｂ2セルには次の式を入力し右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。
=IF(OR($B1="",$A2=""),"",INDEX(Sheet1!$A:$K,MATCH($A2,Sheet1!$A:$A,0),COLUMN(B1)))
この式の場合には1行目にシート1にある項目名が入力されていることが必要です。1行目が空の状態ですと表示がされません。

お礼

KURUMITO様

ご丁寧にありがとうございます。
私の理解不足のため、上記にて更に質問をしてしましましたが、その後無事解決し、ご教示頂いた関数の意味を理解できました。

おかげさまで助かりました。本当にありがとうございました。

回答No.3

KURUMITO

回答Ｎｏ2です。ドロップダウンリストを作成するときに入力する式ですが次のように訂正してください。
元の値の窓には =INDIRECT("Sheet1!A$2:A$40")
大変失礼をいたしました。

お礼

ありがとうございます。
ご教示頂いたとおりにやったら無事転記できました。
ただ、作成中に関数の意味が少々理解できない点があり。。。
sheet1の情報をsheet2に転記する関数で
IF(OR($B1="",$A2=""),"",INDEX(Sheet1!$A:$K,MATCH($A2,Sheet1!$A:$A,0),COLUMN(B1)))

上記の関数の中でまず、IF(OR($B1="",$A2=""),"",の関数であえて空欄を指定する必要があるのでしょうか？

またINDEX(Sheet1!$A:$K,MATCH($A2,Sheet1!$A:$A,0),COLUMN(B1)))
の関数が理解できませんでした。転記自体は綺麗にいったのですが、私の知識・理解度不足のせいでちょっと理屈が分からず。。。

後学のため、出来ましたらこの関数の意味を教えていただけると幸甚です。レイアウト変更指示などが出た際（例えばsheet1のデータをsheet2の同行に転記ではなく、A1,A2,A3と縦に分けて転記する場合、など）に対処できるようにしたいのです。

自分でチャレンジもしてみましたが、理解が及びませんでした。すみません。お願いします。

回答No.1

keithin

できるだけシンプルに応用しやすい方法をひとつ。

シート１のシート名タブを右クリックしてコードの表示を選ぶ
現れたシートに下記をコピー貼り付ける

private sub worksheet_selectionchange(byVal Target as excel.range)
worksheets("Sheet2").range("G1") = Target.cells(1).row
end sub

ファイルメニューから終了してエクセルに戻る。

シート２のＧ１セルに、シート１で選んでいたセルの行番号が常に自動で更新されて出ているので、
=INDEX(Sheet1!A:A,G1)
=INDEX(Sheet1!B:B,G1)
などの式を所定のセルに配置して参照させる。

お礼

keithin様

早々にご回答を頂きまして、ありがとうございました。
keithin様にご教示いただきました方法にても無事転記が出来ましたが、このたびは今後の加工などに備え、私が技能的に追いつきそうな方法をセレクトさせていただきました。
本当にありがとうございました。