Data not staying in sheet #REF!

[KA3PMW]

I am running Windows 7 and Excel 2007. I have data on one sheet (mm-dd-yyyy Emergency Log) that is copied to another sheet (mm-dd-yyyy Log Summary) for a summary that gets sent out to several people. When it is created it works fine but when the files are closed and the summary is reopened all I get is #REF ! in all of the columns.
Any idea why the data is not still there?

 

[oldbrewer]

choose a cell with #ref and tell us what the formula in that cell is

 

[KA3PMW]

choose a cell with #ref and tell us what the formula in that cell is

=IF(INDIRECT("'["&TEXT(TODAY(),"mm-dd-yyyy")&" Emergency Log.xls]FORM'!B"&ROWS($1:4))="","",INDIRECT("'["&TEXT(TODAY(),"mm-dd-yyyy")&" Emergency Log.xls]FORM'!B"&ROWS($1:4)))

If I can figure out how to do it, I can post the sheet for you.

The Emergency Log is not doing the copies. They are all done with the summary using INDIRECT. I think that is the problem. I need something to copy from the log to the summary so the summary does not have any formulas in it.

Unfortunately, due to 3 strokes a few years back I lost what little I did know about Excel. Maybe a macro to copy certain cells or a range of cells when an entry is made or when the Log is closed???

 

[Scott T]

Is Emergency Log.xls open? indirect does not like closed workbook.

 

[KA3PMW]

Is Emergency Log.xls open? indirect does not like closed workbook.

The net control operator, usually me, opens the emergency log which in turn creates mm-dd-yyyy Emergency Log and mm-dd-yyyy Log Summary. Both logs are then kept open until the emergency is over. At that time the log is closed and then the summary. I think the problem is that the summary is dependent on the log and without the log it has no data source. What I think I need to do is copy the cells that I need in the summary from the log to the summary not from the summary to the log.

 

[KA3PMW]

I have tried the following but I get an invalid qualifier error on the line that reads

FilePath2.Sheets("FORM").Range("F2") = FilePath1.Sheets("FORM").Range("C2")

Sub Copycell()
 Dim FilePath1, FilePath2 As String
 FilePath1 = "C:\Skywarn" & "\" & Format(Now, "dd-mm-yyyy") & " Emergency Log"
 FilePath2 = "C:\Skywarn" & "\" & Format(Now, "dd-mm-yyyy") & " Log Summary"
 FilePath2.Sheets("FORM").Range("F2") = FilePath1.Sheets("FORM").Range("C2")

I was using the INDIRECT statement in the summary. Unfortunately I was too stupid to realize that the statement was referring to the cells in a sheet that was not included in the email. The recipients were getting forms full of #REF ! instead of the actual data.