#REF! error appears after deleting a row

[mhorniak]

Hi,
I am getting a sinking feeling that my problem is just how Excel works!?

Attached is a sample of a worksheet in an app that I developed to track and report van ridership usage. After a lot of hours of time effort the form/app works just great!! Except for one detail: if, for whatever reason, a user may discover that one entry needs to be deleted. When he/she uses either the built-in "form" or right clicks and deletes using the rows index column a #REF! appears in certain cells in the row immediately following the deleted row. No subsequent rows develop this error.

The attached workbook has 3 worksheets.
Sheet1 shows the table with 6 rows of data.
Sheet2 is just for a quick way to view the underlying functions and values.
Sheet3 shows the two "#REF!" errors at cells H4 and K4. These appeared after deleting the row with ID: 1002. I kind of understand why it happened i.e. the reference to Cell G3 was momentarily lost!? But note that the row with ID 1004 does not have these errors. In other words their references updated correctly.

What should I do?
Should I create a custom lookup form with deleting capabilities to handle this problem? Or should I rethink my approach and key in on something else? Or??

[humdingaling]

The #REF! error shows when a formula refers to a cell that’s not valid . This happens most often when cells that were referenced by formulas get deleted, or pasted over

what to do?
go to a cell which has the correct it manually
or
find a cell of which you can fill the formula from
in your case H2:K2 fill down
the problem is if you delete the first row of the table (below the header)

[MarvinP]

Hi mhorniak and welcome to the forum,

See if the attached works for you. I changed the formulas on Sheet two so they refer to the same row instead of the row below.

Make Formulas on and about a single row.xlsx

[mhorniak]

Hi mhorniak and welcome to the forum,

See if the attached works for you. I changed the formulas on Sheet two so they refer to the same row instead of the row below.

Attachment 573625

Thanks for the replay MarvinP! Your change removed the if conditions which are needed to make it a dynamic check for each entry.

[mhorniak]

Thanks himdingaling I like your funny handle.

I am aware of the manual approach. But for my users that is not an option. I was hoping someone could really explain why the #REF! error was occurring because the next row (ID 1004) correctly updated. When ID 1003 moved from A5 to A4 after the deletion of ID 1002 the formula should have updated from "=IF(G5=G4,0,G5-F5)" to =IF(G4=G3,0,G4-F4) instead it became =IF(G4=#REF!,0,G4-F4). The three other cells in that formula did correctly update!

[FDibbins]

The best way around this is to NOT delete rows. If necessary, delete the contents of the cells.

The ref error is because the formula is referencing a cell whose entire row have been deleted. The cells below and above, have not had "their" rows deleted, so they show no error.

To see this happen, open a new file or sheet, in A1:Aa3 enter something (doesnt matter what), then in A5, enter =A1 and copy it down twice. Now delete row 2, you will see that (the new) A4 still calcs properly, A5 shows REF and A6 calcs OK. the REF is because you deleted the row that (the new) A5 referenced

[mhorniak]

Hi FDibbins
Thanks for your input and example. Well it is what it is then! There is no magic fix. I'm going to have to come up with a creative solution. Or perhaps use some version of your idea not to delete the row but just the contents. There is a report that gets automatically generated at the end of the app. I'll have to see if your idea will affect its output.

[xlnitwit]

You might alter H2 thus
=IF(G2=INDEX(G:G,ROW()-1),0,G2-F2)
and K2 thus
=IF(G2=INDEX(G:G,ROW()-1),0,MOD(J2-I2,1)*24)

[mhorniak]

What an elegant solution! I will NOT forget it.
Thank you very much xlnitwit.