エクセルで目次から指定の文字列に移動させたい

質問

エクセルで見積書を作成しています。
見積書は提出先の会社ごとにワークシートで分けて、同じ提出先の見積書にはそれぞれ見積書番号を付けています。
ただ、見積書の数が膨大に膨れ上がってきたので目次となるワークシートを作成して、その目次に見積書番号を列挙し、その見積書番号をクリックすると見積書につけてある見積書番号にジャンプできるようにしたいのです。
HYPERLINKではセルへの移動が出来ることは判ったのですが、見積書の変更等で行列が変更することがある為、あくまで目次の番号と見積書の番号をリンクさせたいのですが、どのようにやったら良いのか、どなたか教えて下さい。宜しくお願いします。

回答No.6

m_and_dmp · ベストアンサー

No.5の追伸です。
マクロを使ったことがないとのことですので、少々捕捉します。
Excel 2003ですが、「ツール」→「マクロ」→「新しいマクロの記録」と進めると、マクロの名前と、マクロの保存先が表示されます。マクロの名前は「MacroX」になっていますので、好きな名前に変更します。(Xは1,2,3...）
マクロの保存先は、他の人もこのシートを使うなら「作業中のブック」がいいです。
名前と保存先を決めたらOKして、たとえば文字列検索などの適当な作業をします。
作業が終わったら（終わらなくても、中断でもかまいません。）「記録の終了」ボタンを押します。ボタンがシートの上にない場合は、「ツール」→「マクロ」と進めると、「記録の終了」がありますので、それを選択してマクロの記録を終わらせて下さい。
これで自分が実行した作業がマクロとして記録されます。そのマクロを実行するには、「ツール」→「マクロ」→「マクロ」と進めると、マクロのリストが表示されますので、作ったマクロを選択して、「実行」ボタンを押します。
シート上に適当な図形を作ってそこにマクロを登録し、その図形をクリックして実行することもできます。
作ったマクロを編集したり、自分でコードを記述するときは、「ツール」→「マクロ」→「Visual Basic Editor」と進めます。
Visual Basic Editorが開き、左側の小窓に「VBAProject(作業中のブックのファイル名）の下に、「標準モジュール」があり、それを展開すると作ったマクロがありますので、選択します。そうすると右側の小窓にマクロコードが表示されます。
Sub xxxx()～End Subが一つのマクロです。
私の作ったサンプルマクロを試す場合は、サンプルマクロのSub FindY()～End Subまでをコピーして、既存のマクロのEnd Sub の後ろにペーストしてください。
ご検討を祈ります。

お礼

ご親切に有り難うございます。
何とかクリアできそうです。
本当に有り難うございます。

回答No.5

m_and_dmp

しばらくチェックしていなかったので回答が遅れました。
つぎのマクロを試して下さい。
目次シートに描画ツールで適当な図形を描画し、それを右クリックして、表示されるメニューから「マクロの登録」を選びます。するとマクロのリストが表示されますので、作ったマクロを選択してOKします。
使い方は、目次シートの見積書番号が記述されたセルを選択して、マクロを登録した図形をクリックします。
見積書が、複数のシートにまたがって作成されているものと想定して、ブック全体を検索するようにしてあります。
選択したセルに記述された見積書番号と同じ番号が見つかると、そのシートを選択し、見積書番号が記述されたセルを選択した状態でマクロが終わります。
見積書番号が見つからない場合は「N/A」とメッセージが表示されます。
10行目に　Cells(RN, CN) = ""　というコードがありますが、これは、目次シートの選択したセルに記述された見積書番号を一旦削除する（空白にする）コードです。
一旦空白にしないと、このセルに記述された番号を見て、見つかったと満足してしまい、目次シート以外のシートで検索してくれません。
他に良い解決方法があるかもしれませんので他の方の回答に期待します。
一旦削除した見積書番号は後ろから2行目の　
Worksheets(SN).Cells(RN, CN) = IV
で元の値に書き戻されますのでご心配なく。
12行目の WS.Select　は必要のないコードです。マクロの進行にしたがって、つぎつぎと選択シートを変えていくのが分かるようにして、マクロがちゃんと仕事をしていることを確認するために入れてあります。動くことが確認されたら削除してください。

Sub FindY()
Dim WS As Worksheet
Dim SRCHED As Range
Dim What As Variant
SN = ActiveSheet.Name
RN = Selection.Row
CN = Selection.Column
IV = Selection.Value
What = IV
Cells(RN, CN) = ""
For Each WS In Worksheets
WS.Select
Set SRCHED = Cells.Find(What:=What, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart)
If Not SRCHED Is Nothing Then Exit For
Next
If SRCHED Is Nothing Then MsgBox "N/A" Else WS.Select: SRCHED.Activate
Worksheets(SN).Cells(RN, CN) = IV
End Sub

回答No.4

MackyNo1

遅ればせながら補足回答の意味が分かりました。

行の挿入などに対応してリンク先のセルも自動的に移動させるなら、以下のような数式にしてください。
（Sheet3のE5セルに検索番号が入力されている場合）

=HYPERLINK("[ファイル名.xls]Sheet3!"&ADDRESS(ROW(Sheet3!E5),COLUMN(Sheet3!E5)), Sheet3!E5)

回答No.3

MackyNo1

>教えて頂いた内容ですが、セル同士のリンクになると、見積内容に補足を加えたりすると見積書番号の場所（セル位置）が移動してしまう為に、随時ハイパーリンクを書き換えることになりそうです。あくまで文字列を探したいのですが・・・。

実際に提示した回答を試されたのでしょうか？

私の提示した式を入力しておけば、ハイパーリンク関数で表示されているセル（この見積書番号のセルにハイパーリンクされている）を自動的に探してきますので、そのままの数式で対応できると思うのですが、具体的にどのようなケースで不都合が発生するのでしょうか？

お礼

有り難うございます。
説明不足でした。
伝わりますでしょうか？

補足

説明不足で申し訳ありません。
教えていただいた内容でさせて頂いたのですが、実際に見積をしている中で、行の挿入や削除を頻繁に行います。
また、提出先ごとにワークシートで見積書を振り分け（つまり同じ提出先の見積は同じシートにどんどん保存されていきます）なので、見積書の見積書番号位置（セル）が見積書の書き換えで変わってしまいます。

上手く説明できずに申し訳ありません。

回答No.2

MackyNo1

目次のページに以下のようなHYPERLINK関数を設定してください。

=HYPERLINK("[ブック名.xls]シート名!見積書の番号が記載されているセル番地", 見積書の番号が記載されているセル番地)

例えば、ブック名がBook3で、Sheet3のE5セルに見積書の番号があるなら以下のような式になります。

=HYPERLINK("[Book3.xls]Sheet3!E5", Sheet3!E5)

上記のように見積書の番号は、目次に記載するのではなく、見積書の番号が記載されているセルを参照する形でＨＹＰERLINK関数の第2引数として指定するのが簡単です。

お礼

早速のご回答有り難うございます。
教えて頂いた内容ですが、セル同士のリンクになると、見積内容に補足を加えたりすると見積書番号の場所（セル位置）が移動してしまう為に、随時ハイパーリンクを書き換えることになりそうです。あくまで文字列を探したいのですが・・・。

回答No.1

m_and_dmp

マクロのFind関数を使えばできます。ただし、セルをクリックではなく、「マクロボタンをクリック」になりますが。

目次の、任意の見積もり番号を記述したセルを選択して、CTL+Cにより内容をコピーし、検索ツールを起動して、検索する文字列にCTL+Vで先ほどコピーした内容を入力し、検索場所を「ブック」にして「次を検索」．．．この一連の作業の記録マクロを作成して、作成した記録マクロを編集してできあがり。マクロボタンを目次シートの適当なところに配置して、任意の見積番号を選択してマクロボタンをクリックすると目的の見積書につけてある見積書番号にジャンプする．．．というような方法でできるとおもいますのでチャレンジしてください。

お礼

ご回答有り難うございます。
教えて頂いた内容にチャレンジ中です・・・が、すみません、実はマクロを使ったことが無いんです。回答頂いた内容を順を追ってやっていますが、マクロボタンをクリックするという部分がわかりません。
マクロボタンを配置するやり方から教えて頂けないでしょうか。
宜しくお願いします。

補足

マクロのボタンをクリックするやり方ですが、ボタンクリックではなく、目次に記載した見積書番号をクリックしたら本見積に跳ぶようには出来ないでしょうか？
見積の量が非常に多いのでボタンの数が無限に増えていきます。

すみません、不勉強な上に言葉足らずでした。