Prevent #REF error in formula cells when deleting rows on other sheet

  • (Sorry for a less than ideal subject line - it's the best I could come up with)
    My spreadsheet has two worksheets, call them user and database. User is protected, database is hidden. The user uses a form to enter values, which are then written to the database sheet. The user sheet has formulas that point to the corresponding cell in the database sheet.
    For example, cell User!c2 has formula = database!c2
    (this is a simplification - it's actually more complicated and less pointless, but the effect is the same).
    the problem occurs if I delete a row in the database (it's an option I allow the user).
    When the row is deleted, the formulas in the user sheet are updated to point to the same logical row they pointed to before. For example if I delete row 3 in the database, row 4 in the user sheet now points to row 3 of the database, which is the same logical row that it pointed to before. Likewise, all later rows are updated. The problem is that row 3 of the user sheet now has an invalid reference ie, #REF).

    So, my question: Is there any way to prevent the formulas on user sheet from changing their row reference value?
    I realize that as an alternative I could delete the corresponding row on the user sheet, but this would require unprotecting the sheet first, then deleting, then protecting again. Since I'm not password protecting the sheet, this is OK, but seems kind of clunky.

  • Re: Prevent #REF error in formula cells when deleting rows on other sheet

    Hello Llaves,

    My first choice would be to not delete the rows because it generates this error and there is, as far as I can see, no harm in leaving the row.

    However, to avoid this error you would have to revise your formula to make us of the either INDIRECT or ADDRESS functions to reference the cells.

    INDIRECT is a volatile function, meaning each time you make a change in the sheet Excel re-calculates the entire sheet. This may or may not cause performance issues depending on your worksheet.

    Keeping with your simple example:


    Another way, avoiding use of INDIRECT,


  • Re: Prevent #REF error in formula cells when deleting rows on other sheet

    The problem with leaving the row is that this is what is visible to the user, who should never see an error msg, which is what I would consider a #REF to be.
    The INDEX or INDIRECT functions would do the job, but in my application I need to have many (100's) of rows populated with the formula, which I don't know how to do simply, since the index has to increment for each row, but doesn't do automatically when copied since it's not a row/col ref.
    Thanks for the suggestions, though.

  • Re: Prevent #REF error in formula cells when deleting rows on other sheet

    You could still do that by using a helper sheet which you know for sure will not be touched by the end user (keep it hidden).