データ参照先が別ファイルの場合、式が＃VALUEになってしまう

みなさま、いつもご丁寧にご教授いただきありがとうございます。

今、作成している集計表の元となるデータが別ファイルにあり
それを開きながら数式を組んでいたので気づかなかったのですが
元データを開いていないと数式が＃VALUEになってしまいます。

いろいろ調べたところ、SUMIF関数を使用していることが原因のようです。
※作成している数式にはSUMPRODUCT関数も使用しています。
↓
http://excel-ubara.com/excel3/EXCEL026.html

やはり、元データを開きながら集計表を開く以外に回避する方法はないでしょうか？
私以外にも閲覧する人が社内でいる為、できれば元データを開かずとも
集計表を閲覧できるようにしたいです。

何卒、ご教授願います。

質問者からの補足コメント

• 

  tom04さん
  
  先日は、ご教授いただきありがとうございます。
  ご教授いただいた通りにやってみたのですが、うまくいきません＞＜
  ※マクロのところにも表示されません。
  私のやり方が悪いのだと思いますので、再度ご質問させてください。
  
  myPath = "保存場所のパス" & "\"
  →C:○○\○○\○○\　のようなパスのことですよね？あくまで参照先の場所のパスまでで
  　よいのですよね？
  
  fN = "ファイル名.xlsx"
  →集計しているエクセルファイルの名前でよいのですよね？
  
  何度も申し訳ありませんが、ご教授願います。

  No.1の回答に寄せられた補足コメントです。 補足日時：2016/12/19 09:34

• 

  ご丁寧にご教授いただきありがとうございます。
  
  何度も本当に申し訳ありません。
  再度ご教授いただいた手順でやったのですが、マクロのところに表示されません。。。
  
  下記、再度確認させてください。
  
  私のPCで見るプロパティでは下記になります。
  そのままコピペということでよろしいんですよね？
  
  参照先ファイル保存場所パス
  C:\User\mydesk\Desktop\更新\年間実績
  
  集計用のファイル保存場所パス
  C:\User\mydesk\Desktop\更新\○○部\【引き継ぎ】\計画商品.xlsxの
  「計画商品.xlsx」の部分だけということですよね？
  
  ほんとうにお手数をおかけして申し訳ありませんが
  何卒、よろしくお願いいたします。

  No.2の回答に寄せられた補足コメントです。 補足日時：2016/12/19 17:32

No.3 ベストアンサー 回答者： tom04 回答日時： 2016/12/19 18:17

続けてお邪魔します。

おそらく「ファイル名」の部分が違っていると思われます。
「参照先ファイル」の「保存場所のパス」と「ファイル名」です。

No.2で投稿したように同じ「プロパティ画面」に表示されている
「参照先ファイル保存場所パス」が
「C:\User\mydesk\Desktop\更新\年間実績」になっていると思います。
同じプロパティ画面で一番上に書いてあるのが「ファイル名」になります。
何となっているでしょうか？
そのままそれを「ファイル名」にします。
必要なのは「参照先ファイル」のファイル名です。

※　マクロコードは「集計用」Book（SUMIF関数などが入っているSheetのファイル）
の「This Workbook」のVBE画面に記載するコードのはずですので

＞C:\User\mydesk\Desktop\更新\○○部\【引き継ぎ】\計画商品.xlsxの
「計画商品.xlsx」の部分だけということですよね？

とある「集計用」ブックのファイル名等は全く不要です。

※　気になる点として・・・
＞マクロのところに表示されません。。。
ですが、
単に「参照先ファイル」を開き、「This Workbook」をアクティブにしているため
見た目には何も変化ありません。
画面下側、タスクバーのExcelのアイコンをポイントしてみてください。
Excelファイルが二つ表示されると思います。

そしてSUMIF関数などに手を加えてみてください。
おそらくエラーにはならないはずです。m(_ _)m

この回答へのお礼

tom04さん

こんにちわ。
何度もお手数をおかけいたしました。

ご教授いただいたやり方で無事できました!!

私の理解不足で何度もお手数をおかけしたにもかかわらず
ご丁寧にご教授くださり、ありがとうございました。
感謝しております。

また、わからないことがあればご教授いただけますと幸いです。

よろしくお願いいたします。

お礼日時：2016/12/21 11:18

No.4 回答者： tom04 回答日時： 2016/12/19 19:33

たびたびごめんなさい。

No.1のコードはコード記載のファイルを開いたときに、参照先のファイルを一緒に開くコードですので、
まず上書き保存をして一旦ファイルを閉じ、再び「集計用」ファイルを開いてください。
これで「参照先ファイル」も一緒に開くはずです。

※　「集計用」ファイルを閉じると
参照先ファイルも閉じるようにしています。m(_ _)m

この回答へのお礼

tom04さん

再度、ご教授いただきありがとうございます。
また、何度もお手数をおかけして申し訳ありません。

やはり、わたしの誤理解でしたね…。
マクロと他のこと(今回ご教授いただいたコード)との違いが理解できておらず、大変失礼しました。

明日、再度挑戦してみます。

いつも本当にありがとうございます。

お礼日時：2016/12/20 10:47

No.2 回答者： tom04 回答日時： 2016/12/19 16:42

No.1です。

保存場所のパスとファイル名の件ですが、
一番簡単なのは
保存場所のフォルダを開く　→　ファイル（ファイルのアイコン）上で右クリック　→　プロパティ

その中の「場所」の部分が「保存場所のパス」になります。
コードにそのままコピー＆ペーストするだけでOKです。
※　注意点　※
コード内で「\」を付け加えるようにしていますので
最後の「\」がダブらないようにしてください。

同じくプロパティの一番上側に「ファイル名」が表示されているはずです。
そのファイル名に拡張子（.xlsx　.xlsm　.xls　など実際の拡張子）を付け加えてみてください。

※　前回のコードではExcel2007以降のファイルだと思ったので
＞fN = "ファイル名.xlsx"
としていました。m(_ _)m

No.1 回答者： tom04 回答日時： 2016/12/16 22:12

こんばんは！

いっそのことVBAで
数式がSUMIF関数や・SUMPRODUCT関数が入っているブックを開いたときに
参照先のブックも開く操作にしてみたらどうでしょうか？

SUMIF関数などが入っているブックにコードを記載します。
Alt＋F11キー　→　VBE画面が表示されますので、左側の「ThisWorkbook」上でダブルクリック　→　VBE画面が表示されますので
カーソルが点滅しているところに↓のコードをコピー＆ペースト

Dim myPath As String, fN As String 'この行から//
Private Sub Workbook_Open()
myPath = "保存場所のパス" & "\"
fN = "ファイル名.xlsx"
Workbooks.Open myPath & fN
ThisWorkbook.Activate
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
fN = "ファイル名.xlsx"
On Error Resume Next
Workbooks(fN).Close
If Workbooks.Count = 1 Then
Application.Quit
ThisWorkbook.Close
End If
End Sub 'この行まで//

※　コード内の「保存場所のパス」は参照先ファイルの保存場所のパスになります。
保存場所のファイル上で右クリック　→　プロパティ　から確認できます。
「ファイル名」は実際のファイル名にしてください。

※　保存時にはマクロ有効ブックとして保存してください。

何とか見た目にはご希望に近い形にならないでしょうか？m(_ _)m

この回答へのお礼

tom04さん

いつもご丁寧にご教授頂き、感謝しております。

仰る通り、その操作ができれば嬉しいです。
ご教授頂いた内容で月曜日対応してみますね。

また、不明点あればお尋ねいたしますのでその際はお手数おかけしますが、何卒よろしくお願いいたします。

お礼日時：2016/12/16 23:05