EXCEL 一覧シートから各シートへ参照

質問

現在シートが
一覧、1、2、3、　とあります。

一覧シートには、

シート名(A1) 名前(B1)
1(A2) 山田(B2)
2(A3) 鈴木(B3)
3(A4) 木村(B4)

と記載されております。（カッコ内は、セルの位置です）

シート　1　のA1には、山田 シート１のB2を参照
シート　2　のA1には、鈴木 シート１のB3を参照
シート　3　のA1には、木村 シート１のB4を参照
とシート　一覧から参照して表示されるようにしたい　と考えております。

数字の名前のシートが数個であればシート毎に入力すれば済む話ですが、
予定では、順次増え、最終的には100位になる予定です。

数式、マクロ　VBA　等を使って　
一覧シートに名前を入力すると該当のシートのA1セルに参照させる事は可能でしょうか？
よろしくお願いいたします

回答No.2

moon00 · ベストアンサー

シート　1　のA1に以下の式を入力

=VLOOKUP(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))*1,一覧!A:B,2,FALSE)

これで、一覧シートのA列からシート名を検索し、B列にある名前を返します。
このシートをコピーして、シート名を変更すれば、それぞれのシート名をA列から探して、その横の名前を
表示してくれます。

お礼

まさにビンゴでした。
ありがとうございました。

回答No.3

tom04

こんばんは！

Sheetは　1・2・3・山田・鈴木・木村・・・とSheetがすでにあるとして
仮に「一覧シート」のA3セルに「2」・B3セルに「鈴木」と入力した場合は
「2」というSheet名のシートのA1セルに
「鈴木」というSheet名のB3セルの値を返せばよい！というコトですかね？

VBAになってしまいますが、そう解釈しての一例です。

画面左下の「一覧」Sheetのシート見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストして「一覧」SheetのA・B列にデータを入力してみてください。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
Dim i As Long, k As Long, str1 As String, str2 As String, myFlg As Boolean
If Application.Intersect(Target, Range("A:B")) Is Nothing Or Target.Count <> 1 Then Exit Sub
With Target
i = .Row
If .Column = 1 Then
str1 = .Value
For k = 1 To Worksheets.Count
If Worksheets(k).Name = str1 Then
myFlg = True
Exit For
End If
Next k
If myFlg = False Then
MsgBox "入力したシートが存在しません、" & vbCrLf & "存在するシート名を入力してください。"
.Select
Exit Sub
End If
Else
str2 = .Value
For k = 1 To Worksheets.Count
If Worksheets(k).Name = str2 Then
myFlg = True
Exit For
End If
Next k
If myFlg = False Then
MsgBox "入力したシートが存在しません、" & vbCrLf & "存在するシート名を入力してください。"
.Select
Exit Sub
End If
End If
End With
If WorksheetFunction.CountBlank(Range(Cells(i, "A"), Cells(i, "B"))) = 0 Then
str1 = Cells(i, "A")
str2 = Cells(i, "B")
Worksheets(str1).Range("A1") = Worksheets(str2).Range("B" & i)
End If
End Sub 'この行まで

こんなんではどうでしょうか？m(_ _)m

お礼

ありがとうございました
まさにこの通りです。

回答２の方の関数を使う方法が　
今の私の技量では応用が利きそうです。

せっかくアドバイスくださったのに申し訳ございません。

少しＶＢＡを勉強して　じっくり参考にさせて頂きます

回答No.1

keithin

んーと？
＞シートが　一覧、1、2、3、　とあります。

が間違いで、プラス「山田」シート、「鈴木」シート、「木村」シートがそれぞれあるんですね？「山田シート１」とかも間違いで。

準備：
ブックを「必ず」一回保存しておきます。
一覧のＡ列にもしも本当に１，２，３と記入しているのなら、Ａ列を一回選択、セルの書式設定の表示形式を文字列に設定、改めて１，２，３を記入し直します
１２３は「実はウソ」で１，２，３「じゃない言葉」をＡ列に記入している場合、それが原因で計算が通らなくなるので必ず行ってください。
それから「半角数字の123」と「全角文字の１２３」の記入間違い、シート名間違いにも十分注意してください。

＃練習
１，２，３の各シートの仮にＣ１セルに
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)
と記入、自シート名が計算できることを確認してください。

＞シート　1　のA1には、山田シート１のB2
＞シート　2　のA1には、鈴木シート１のB3
　：

それぞれＢ２、Ｂ３…のように参照したいで間違いないのですね。

手順：
シート１，２，３のＡ１には
=INDIRECT(VLOOKUP(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99),一覧!A:B,2,FALSE)&"!B"&MATCH(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99),一覧!A:A))
と記入します。

解答の手順と勝手に違う事をするとエラーになるので、キチンと回答した通り漏れなく行ってください。

お礼

ありがとうございました。
各シートの特定の部分を一覧に表示させることは容易にできましたが、
一覧シート以外のシートはすべて同じフォームで
一覧シートにある各個人の氏名をそれぞれのシートの氏名欄にに反映出来ないものか
考えておりました。修正や増やす、
今から作成する場合　いちいち該当のシートに移動して入力するよりも
一覧で入力できた方が楽だと考えたからです。
（シート　”１”は山田さん用　”２”は鈴木さん用）

説明不足で申し訳ございません
また機会がありましたらよろしくお願いします。