(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.