Excel 2007 - Formula changes to #REF

Question

So I've got this Workbook which contains a lot of data. And I've got this one sheet which basically copies the data based on certain conditions.

Each cell in each row looks like this (the last specified cell is the one where the formula is in):

=IF(Numbers1!E2<>0;Numbers1!A2;"")
=IF(Numbers1!E3<>0;Numbers1!A3;"")
=IF(Numbers1!E4<>0;Numbers1!A4;"")
=IF(Numbers1!E2<>0;Numbers1!B2;"")
=IF(Numbers1!E3<>0;Numbers1!B3;"")
=IF(Numbers1!E4<>0;Numbers1!B4;"")

So the formula in cell A2 is the first one, formula in A3 is the second line etc.

I want to copy the value from the same column and row from the sheet Numbers1, IF the value in the same row of column E is not 0. This seems to be working just fine.

But, when I update the data in Numbers1 sheet, the formulas are all of a sudden invalid and the formula now looks like this:

=IF(Numbers1!#REF!<>0;Numbers1!#REF!;"")

Each formula in each cells look identical to the formula above. And I can't have that, why can't Excel just keep the formula as it is without "helping" me?

What method are you using to update the data in the 'Numbers1' worksheet? If you use cut/paste, Excel destroys the old cell references and overwrites them with the cells that it "moved". If you use copy/paste, I believe it shouldn't do this. — Farray
The data in Numbers1 worksheet are imported via a macro. Perhaps the cell references are overwritten because of that? If that's the case, perhaps I need to re-insert the references for each cell using macro' — Kenny Bones
Do you have access to the macro that is importing the data? I wouldn't be surprised if it is bulk-deleting the rows and then adding new data instead of clearing existing data. If so, the easy thing would be to change the method of data update. If not, the next best thing would be to write a macro to re-write your formulas after import. — Farray
I think the latter would be the best, because the macro is written by the company that delivers the software that reads the data from a database. So it's kind of "their property". But I would need a Sub to do this without much code in that case. Each formula is essentially the same, only difference is the reference to the cell the formula is in. So if it's easy to get the cell number from the cell "I'm in", that would make it easier to create the formula. I'll try it out myself, but if you have any suggestions, I'm happy to try them out :) — Kenny Bones

Farray Farray · Accepted Answer · 2011-07-11T06:19:27

Since you may be better off using a macro to rewrite your formulas, here are the basics:

Sub RewriteFormulas()

    Dim row, col As Integer
    row = 1               'row you want your target formulas to be on

    For row = 1 To 60
        For col = 1 To 13

            ActiveSheet.Cells(row, col).Formula = "=IF(Numbers1!" & Cells(row,col).Address & "<>0,Numbers1!" & Cells(row+2,col).Adddress & ","""")"

        Next row
    Next col

End Sub

You can play around with using different sheets (or different workbooks) instead of just ActiveSheet so you can have 1 workbook that stores the macro and alters data in whatever workbooks provide your updated datasets.

Hope that helps...

Excel 2007 - Formula changes to #REF

1 Answers