Losing Formulas in Excel

[Guest]

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.

 

[Guest]

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.

 

[Guest]

When I removed the $ the problem still exists. I am using one workbook and
referencing worksheets within the same workbook.

Thanks

 

[Fred Smith]

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.