Data not staying in sheet #REF!

KA3PMW

New Member
Joined
Jul 25, 2016
Messages
38
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?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

KA3PMW

New Member
Joined
Jul 25, 2016
Messages
38
choose a cell with #ref and tell us what the formula in that cell is

Code:
=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???
 
Upvote 0

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,635
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Is Emergency Log.xls open? indirect does not like closed workbook.
 
Upvote 0

KA3PMW

New Member
Joined
Jul 25, 2016
Messages
38
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.
 
Upvote 0

KA3PMW

New Member
Joined
Jul 25, 2016
Messages
38
I have tried the following but I get an invalid qualifier error on the line that reads
Code:
FilePath2.Sheets("FORM").Range("F2") = FilePath1.Sheets("FORM").Range("C2")

Code:
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.:(
 
Upvote 0

Forum statistics

Threads
1,186,542
Messages
5,958,388
Members
438,356
Latest member
Hnajibeddine

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top