Hi Ben,
I'm only learning too so if there is someone with a better suggestion,
*please speak up*.

Yes, you are right, excel is trying to adjust the formulae but "A
display of #REF! means that your formula refers (directly or
indirectly) to a cell that no longer exists, due to a change in the
worksheet/workbook/other precedents."
In your case, the cutting & pasting you are doing changes the worksheet
structure & therefore your formulae *will not work*/be accurate - as you
have shown with your example. I'd recommend copying the "if" formula
down from the top row of all your status columns so you know it is
referencing the correct cells. I suspect that any cutting you have done
in the past will have thrown some of the formulae out of sync so that
even some that work are not referring to the correct row. This can be
easily checked by selecting a cell in column E & pressing F2 - the
cells that are being referenced are usually highlighted when this is
done.

I still question the need for cutting & moving the data around b/c I
assume it is being moved due to a change in the "status" column. If
this is the case, have you considered using data - sort, & rearranging
the rows based on column E?
However, I've had a play & modified the below for you. It isn't the
"ideal" that you requested but may be better (?) as it includes both "a
copy + stores the range and then pastes & delete/clears the stored range
from earlier". It appears to work but I haven't been thorough with error
testing/trapping.
NB, this macro deletes the complete rows which your copy range was on
to prevent the "#ref!" problem. However, if you have more information
in column F & further right, remove the 2 lines ending with '* &
replace with line1 "Range(MyAddr).Delete Shift:=xlUp" and line2
"range("e2").copy Range("e2", Selection.End(xlDown))".


Sub CopyPasteAndDeleteOriginalRange()
'sourced (& modified) from
http://www.experts-exchange.com/Appl..._20629300.html
Dim prompt, sDlgTitle As String
prompt = "Select a range"
sDlgTitle = "Get User Range"
On Error Resume Next
Set URng = Application.InputBox("Select range", sDlgTitle,
ActiveCell.Address, Type:=8)
MyAddr = URng.Address
Set PasteRng = Application.InputBox("Select range", sDlgTitle,
ActiveCell.Address, Type:=8)
MyPasteAddr = PasteRng.Address
If URng Is Nothing Then Exit Sub
'COPY, PASTE, & DELETE CODE HERE USING THE RANGE DEFINED BY YOUR
SELECTION.
Range(MyAddr).Copy Range(MyPasteAddr)
Range(MyAddr).EntireRow.Select '*
Selection.Delete Shift:=xlUp '*
MsgBox "Done"
End Sub

Hth
Rob Brockett
NZ
Still learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=499117