How to prevent “#REF!

Hi Experts,

I need Experts help. Is that a way to retain the actual time at Column A if the data in Column D are removed. At present if I removed Column D, data in Column A become “#REF!. How to avoid this?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You could copy the values in Column A and then use Paste Special->Values.

Highlight the cells whose value you want to preserve.  Press Ctrl-C to copy.  Then right click anywhere in the highlighted region and select Paste Special-->Values (in Excel 2007 the icon is a clipboard with "123" printed on it).
If you clear column D, the error does not appear but there is no math going on, either.  When you remove a column or cell with delete, any dependent formulas are impacted.

The formula in Column A looks at the row prior, then adds duration from Column D to arrive at the next Column A time.  It appears duration is something that varies based on title, etc., so you need that.

However, if you want to keep Column A exactly as is, just select column A (click on the A at the top) and then hit Copy, Paste Special, Values, to convert the formula to values.

Alternatively, you can HIDE column D, or relocate it somewhere else.  That way it won't be on the viewable/printed form, but the math to come up with the next time will still be valid.

Please advise if this helps.

Billa7Author Commented:
Hi Dave,

It helps at certain point but it's look like I'm trying to find an alternative way to solve my actual problem which is not giving me a right solution. Frankly speaking, I'm having dilemma to resolve this issue.    
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Try using the offset function when pointing to other cells in your function. This will avoid the #Ref when the cell or row is deleted and the formula would pick up the next cell or row as specified in the offset function

The OFFSET function works much like INDEX, because it typically uses results from a MATCH function to return a reference. It takes this form:

=OFFSET(reference, rows, cols, height, width)
  reference: a range in an Excel spreadsheet.
  rows: the number of rows to shift the top-left cell of the result from the top-left cell of the reference.
  cols: the number of columns to shift the top-left cell of the result from the top-left cell of the reference.
  height: the number of rows to return.
  width: the number of columns to return
Billa7Author Commented:
Hi bhaskarss,

Any chance for you to provide me the OFFSET  using my sample data (workbook)?
using the OFFSET function, your formula in cell A9 would be:

This won't necessarily save your time values in column A, but it will allow your formulas to still work if column D is deleted.
Why would you want to delete column D - perhaps if you explain that, and what you want column A to have, then I can revise your solution.

Addressing column D in a different fashion might remove #REF! errors, but it doesn't address the issue that column A would have an invalid value.  Why would you need to consider OFFSET or any other method of addressing column D?  Please clarify.

What, exactly are you trying to accomplish?

Billa7Author Commented:
Hi Dave,

Actually I had problem with a macro whereby this macro able to update the timing at Column A based on duration at Column D (  =  Column A + Column D), however it has 2 major problem,

1. The time doesn't change automatically if any of the cell at Column D is modified. E.g. if if I modified cell D32, only cell A33 will be updated, whereas other cell at Column A , e.g. A34,A34..were not updated.
2. If transition time at midnight (00:00)  e.g. A21 (22:30; 1-Dec) and the total duration at column D21 is 01:00, cell A22 will get updates with 23:30 by right this not suppose to happen. The cell at A 25 need to be updated if total duration is crossing midnight. e.g. if duration at D21 is 2:30, cell A25 will be updated as 01:00.

If  I got fix these 2 issues, I no need to have an alternative solution (I ca remove column D without any issue). I have attached the said macro in this this workbook.
I'm a bit confused by your comments, because you have no support for the time if there is no duration anymore.  Sure, you can delete column B, but then A would then be static, and ANY entries in column D could create macro errors.

The macro, unfortunately, is not intuitive enough to cascade the formulas down based on the change at D32.

You might be better off putting the formulas back in - which makes this more easy to debug, then have a macro to convert those formulas to values when you're done and don't need the duration column, anymore. wabashbdw suggested a manual approach to that.

Here's the code, in a public module:

Sub removeDuration()
Dim wks As Worksheet

    Set wks = ActiveSheet
    wks.Range("A:A").Value = wks.Range("A:A").Value 'converts time formulas
    wks.Range("D:D").Clear 'clears column D
    MsgBox "De-coupling time formulas complete!"
End Sub

Open in new window

See attached with the original sheet and a button/macro to convert column A to value, disconnecting it with column B.  Now, there won't be macro errors, for entering different data in column D, etc., after you've deleted column D.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Billa7Author Commented:

Thanks a lot for the help. Merry Christmas.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.