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, 2022 in Database by Edureka
• 13,670 points
282 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, 2022 by gaurav
• 23,260 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
1,549 views
0 votes
1 answer

#NAME? error in Excel for VBA Function

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

answered Apr 5, 2022 in Database by gaurav
• 23,260 points
1,065 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, 2022 in Database by gaurav
• 23,260 points
653 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, 2022 in Database by gaurav
• 23,260 points
1,131 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, 2022 in Database by gaurav
• 23,260 points
1,473 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, 2022 in Database by gaurav
• 23,260 points
3,500 views
0 votes
1 answer

Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452) in sql 2008

Solution Go to Start > Programs > Microsoft SQL Server > Enterprise Manager. Right-click the SQL ...READ MORE

answered Sep 12, 2022 in Database by narikkadan
• 63,420 points
833 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
2,277 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, 2022 in Database by gaurav
• 23,260 points
17,473 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, 2022 in Database by gaurav
• 23,260 points
691 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP