Excel problem for beginners REF Error When referenced Row is deleted or Added

0 votes

I have data in column A which is copied in another random location. The copied data could also be in column B-Z, AA... Etc, Or alternatively could be on a new sheet by setting the column =Sheet1!A1 for example.

In the attached image I initially had a,b,c...through to f. In this example,data is copied in the same column just further down by entering at A10 =A1 and copying down as required.

enter image description here

When what was A5 is deleted as a row to remove 'e' you can see that A14 now has the #REF! Error. This is strange to me because although the row is deleted the data has shifted up one. so A14=A5 which previously returned 'e' should now return 'f' but instead returns the error. Initially I thought that this is because the data is in the same column but the same error is returned if on another sheet for example If I set A1=Sheet1!A1 and then delete a row on sheet1.

If you drag down the copying formula again the error is corrected But surely this can be made to happen automatically? Especially if dealing with large amounts of data.

Surprisingly this error doesn't seem to have an obvious solution posted online. I wanted to create this post for anyone to suggest feasible solutions for those starting out with excel (including myself).

Apr 1 in Database by Edureka
• 9,320 points
22 views

1 answer to this question.

0 votes
You'll run into this problem if you employ a broad formula like =A5. Internally, Excel is not designed to manage this.

The OFFSET function is the easiest approach to avoid this mistake. Because this function is dynamic, it will update as new or deleted cells are added or removed.

I hope this solves your issue.
answered Apr 4 by Edureka
• 8,820 points

Related Questions In Database

0 votes
1 answer

Which one is suitable for real time big database- Primary Key or Unique Index?

What is a unique index? A unique index ...READ MORE

answered Oct 12, 2018 in Database by Frankie
• 9,830 points
977 views
0 votes
1 answer

#NAME? error in Excel for VBA Function

Because you have a module with the ...READ MORE

answered Apr 5 in Database by Edureka
• 8,820 points
31 views
0 votes
1 answer

I have a Run Time Error 91 for an Excel Add In

When there was no unhidden workbook open ...READ MORE

answered Apr 6 in Database by Edureka
• 8,820 points
24 views
0 votes
1 answer

How to deal with "Microsoft Excel is waiting for another application to complete an OLE action"

The first step in troubleshooting is to ...READ MORE

answered Apr 6 in Database by Edureka
• 8,820 points
44 views
0 votes
1 answer

Excel indirect reference produces a #ref error

The formula =SUM(B2,C2,D2) is used in column ...READ MORE

answered Apr 11 in Database by Edureka
• 8,820 points
20 views
0 votes
1 answer

Excel VBA run-time error 1004 : Application-defined or object-defined error

Maybe your code is on Sheet 1, ...READ MORE

answered Apr 11 in Database by Edureka
• 8,820 points
39 views
0 votes
1 answer

When is the functional dependency known as fully functional dependency?

The criteria of fully functional dependency, is ...READ MORE

answered Aug 10, 2018 in Database by DataKing99
• 8,240 points
1,619 views
0 votes
1 answer

Which operator is used in the query for pattern matching?

LIKE operator is used for pattern matching, ...READ MORE

answered Oct 15, 2018 in Database by DataKing99
• 8,240 points
12,538 views
0 votes
1 answer

I have a Run Time Error 91 for an Excel Add In

"Runtime Error 91: Object variable or with ...READ MORE

answered Mar 30 in Database by Edureka
• 8,820 points
37 views
0 votes
1 answer

Why is there still a row limit in Microsoft Excel?

Because of optimizations, most likely. There are ...READ MORE

answered Mar 30 in Database by Edureka
• 8,820 points
23 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP