 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / September 2007Losing Formulas in Excel
I have a worksheet (#1) that references another worksheet (#2) line by line and copies select information into the sheet. When I delete lines in #1 the #2 comes back with #REF! for the lines that I deleted in #1. Data in #2 is imported into another application and the format is important. This happens because the default reference for referring to another workbook is absolute ($A$1). when the row is deleted it no longer knows what to reference. The only solution is to make the reference non-absolute (remove the dollar signs), and make sure that you only delete rows when you have both workbooks open. Deleting rows in wkbk #1 without having #2 open will make the references in #2 refer to the wrong cells.  SignatureJNW > I have a worksheet (#1) that references another worksheet (#2) line by line > and copies select information into the sheet. When I delete lines in #1 the > #2 comes back with #REF! for the lines that I deleted in #1. Data in #2 is > imported into another application and the format is important. When I removed the $ the problem still exists. I am using one workbook and referencing worksheets within the same workbook. Thanks > This happens because the default reference for referring to another workbook > is absolute ($A$1). [quoted text clipped - 12 lines] > > #2 comes back with #REF! for the lines that I deleted in #1. Data in #2 is > > imported into another application and the format is important. When you import, do you still need the formulas, or do you just need the values? If all you need are the values, you can convert your data to values by using Copy>Paste Special...>Values. Then when you delete the lines, there are no formulas, therefore no #REF errors. SignatureRegards, Fred > When I removed the $ the problem still exists. I am using one workbook and > referencing worksheets within the same workbook. [quoted text clipped - 18 lines] >> > #2 comes back with #REF! for the lines that I deleted in #1. Data in #2 is >> > imported into another application and the format is important. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.