Home > How To > Ref Error When Deleting Row

Ref Error When Deleting Row


I would like users to insert rows (this is straight forward as in Excel 2003 i can give them the permissions), but i would also like the formulas from the cells Share Share this post on Digg Del.icio.us Technorati Twitter "The greatest challenge to any thinker is stating the problem in a way that will allow a solution." Bertrand Russell Reply With Press CTRL+A to select all of the cells, go to format --> cells --> protection and uncheck "locked". Solution If you accidentally deleted rows or columns, you can immediately click the Undo button on the Quick Access Toolbar (or press CTRL+Z) to restore them.

Reason: deleted quote Register To Reply 09-20-2010,07:45 AM #4 romperstomper View Profile View Forum Posts Visit Homepage Forum Guru Join Date 11-04-2008 Location A1 MS-Off Ver Most Posts 12,299 Re: avoid I don't mind redoing whole spreadsheet, it was done ages ago in a very fast manner. because there are no cells above row 1. Ask Your Own Question Problem: Cut-and-paste Is Altering Formulas - Excel Excel Forum I am making a spreadsheet for several users, with columns A and B for user input and columns Visit Website

How To Fix Ref Error In Excel

error because it’s looking for a value to return from column 5, but the reference range is A:D, which is only 4 columns. eg if you wish to preserve the formulae but not the values why not just blank out 1,3 & 5 instead of deleting the row? Benjamin Franklin (1706 - 1790) The secret of genius is to carry the spirit of the child into old age, which means never losing your enthusiasm. You can use the INDEX function to avoid these errors.

Share Share this post on Digg Del.icio.us Technorati Twitter Give a man a fish, he'll eat for a day. In the first row of that column put 1 to give it a start value . ExcelIsFun 20,246 views 14:27 Excel Tips - Tip#20: Common errors when using VLOOKUP - Duration: 6:49. How To Prevent Ref In Excel I would greatly appreciate this!

Sheet 1 contains rows of data that changes from week to week. Ask Your Own Question Insert Rows And Autofill Formulas In Protected Sheet? - Excel Excel Forum I have a a spreadsheet where i have protected ranges. TeachExcel 103,267 views 3:02 Loading more suggestions... have a peek here Click the Back button to try another link.

For example the formulas in sheet 2 populate A2:BZ2, Sheet 3 C2:BY2 and sheet 4 A2:Z2. Prevent #ref Up next #REF! Sign in to make your opinion count. Ask Your Own Question Deleting Rows But Keep Formulas - Excel Excel Forum Hi All, I'm trying to delete zero rows, keep blank rows, but preserve formulas.

Preventing #ref! Errors When Deleting Rows

keep-formula Finally, here's the question - What needs to be added/changed to make the first sub-totals zeros instead of #REF!? http://www.brighthub.com/office/home/articles/1950.aspx Then, use tools --> protection --> protect sheet and check everything except "delete rows" and "delete columns". How To Fix Ref Error In Excel The indirect function accepts a string value as the input parameter. Can You Make A Cell = 0 If Original Function Is Creating A"#ref!"? Theme.

Is there a way to resolve this either in Excel or using VBA? Ask Your Own Question Drag And Drop,vba - Excel Excel Forum I have several mickey mouse formulas in different rows that take different data from the file, i usually select all This may require editing the macro in the Visual Basic Editor (VBE) to take that situation into account. I have Office 2010 and have a table linked to an external ODBC connection. Excel Delete Row Without Affecting Formula

All other cells are locked to prevent users from amending the formulas. Teach a man to fish, he'll eat for a lifetime. Why were Native American code talkers used during WW2? The problem I am having is once the code has reached the end of my list it continuously loops because the last row has zero values.

errors in formulas that referenced said rows. Excel Delete Row Formula Reference Register Help Forgotten Your Password? Thx.

Uploaded on Nov 16, 2009See how to use the OFFSET function to avoid the #REF!

However sometimes the amount of rows used in sheet 1 can be as little as 10 rows and sometimes as large as 9,600. However, if users do not enter enough data, then my formulas return an error value which causes my array formulas to also return errors. I tried =INDIRECT(DataJobsAllAmend!A407) but that doesn't work and I do believe INDIRECT is volatile and will cause recalculation issues (my workbook will be pretty big soon). 15 commentsshareall 15 commentssorted by: top How To Delete Columns In Excel Without Affecting Formula row 2 to row 4).

That's your new favorite. I think that'll work. I tried using absolute cell references, but that doesn't work either (=$B$2 becomes $B$3). Here is the FAQ for this forum. + Reply to Thread Results 1 to 13 of 13 avoid "REF" error when deleting data Thread Tools Show Printable Version Subscribe to this

ie =Offset(A1,407)... I suppose I could copy all the formulas into an array and then recopy them into the sheet, but I'd ideally like to have them not disappear in the first place. Register To Reply 09-20-2010,08:58 AM #11 bshelman View Profile View Forum Posts Registered User Join Date 05-05-2008 Posts 45 Re: avoid "REF" error when deleting data Originally Posted by romperstomper Oh,