• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 786
  • Last Modified:

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?



Time.xls
0
Billa7
Asked:
Billa7
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
wabashbdwCommented:
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).
0
 
dlmilleCommented:
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.

Dave
0
 
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.    
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
bhaskarssCommented:
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
0
 
Billa7Author Commented:
Hi bhaskarss,

Any chance for you to provide me the OFFSET  using my sample data (workbook)?
0
 
wabashbdwCommented:
Bill-
using the OFFSET function, your formula in cell A9 would be:
=SUM(A8,OFFSET(A8,,3))

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.
0
 
dlmilleCommented:
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?

Dave
0
 
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.
Time--2-.xls
0
 
dlmilleCommented:
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.

Dave
Time.xls
0
 
Billa7Author Commented:
Hi,

Thanks a lot for the help. Merry Christmas.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now