Deleting rows w/out losing references

**\/\/avefor/\/\** · 09-23-2008, 01:58 AM

I have a sheet of data that I'm trying to delete a row from. When I delete the row, all the rows below it change to #REF, and I guess I lose all the references to my formulas.

I had a similar sheet of data that I could delete rows from without this happening, so I know it can be done, but how?
Let me know if any further info is needed to help solve this problem.
Thank you.

**shg** · 09-23-2008, 02:22 AM

Welcome to the forum.

so I know it can be done, but how?

Indeed, but it depends on what the formulas are.

**sglife** · 09-23-2008, 02:49 AM

You can attach your file here.
I believe it will be easy to solve

**\/\/avefor/\/\** · 09-24-2008, 01:46 AM

Originally Posted by sglife

You can attach your file here.
I believe it will be easy to solve

Sure, here you go.
I had to zip it up because it was too big.
Let me know if you need anything else.
Thank you.

**sglife** · 09-24-2008, 01:58 AM

I have checked your formulas, there are some formulas refer to the above row, so if you delete a row, like row 15, then the row 16 will show a "#REF",
To solve this problem, just copy the formula from row 13, and paste down to all the field
and remember to unhide column P to column AE because there are some formulas too

**shg** · 09-24-2008, 02:32 AM

sglife's approach is certainly valid if you don't mind copying formulas down after inserting or deleting a row.

In the alternative, you can used relative named ranges, and make the sheet immune from the effects of inserting or deleting rows. As an example, A14's formula is =A13+1. If row 13 is deleted, you get a reference error.

Select cell A14 and do Insert > Name > Define ptrAbv Refers to: =!A13 (NB: EXACTLY as shown)

Then change the formula to =ptrAbv+1. That formula always refers to the cell directly above it, irrespective of where it appears.

The same approach is valid for all of your references, differing slightly in the details. Post back if you need a second example.

**isonj** · 09-24-2008, 02:34 AM

The other option would be to copy the area (from the line below the line you want to delete) plus one extra line, then paste over the line you wanted to delete. This way the formulas will not go to a #REF.

**\/\/avefor/\/\** · 09-24-2008, 07:11 PM

I really appreciate you guys' help.

The ptrAabv method works great when I'm counting, but when I tried to do it with all the other cells in the row, it started giving me errors, and I really didn't want to have to make a pointer to every single cell from A to the last cell in the row, which I think was W.

The method to cut all the rows below and paste them over the row I'm deleting works, I guess, but what if I need to add a row?
It's frustrating because I tried to add one today, and it it's keeping the references to the cell's 2 rows above it now. I'm thinking there has got to be a better way to do this. What good would it be to not be able to add or delete rows w/out screwing up the whole sheet?

**shg** · 09-25-2008, 12:28 AM

I'm thinking there has got to be a better way to do this.

There is, and I thought I gave you a pretty big hint. I do this more than infrequently.

If you don't understand, please explain exactly what you tried that didn't work.