imitating cut and paste - #REF! problem fix
 

Hello,

I once wrote here due to a problem I was having when cutting and pasting a
worksheet of mine. Here's a brief outline of the problem based on one of my
simpler worksheets and the solution I worked out.

I have cols for check number, amt($), recipient, date and status. I will
frequently cut and paste cols A to D (not E which is status) either to a row
further down or to a cashed check section to the right of cols A to E (cols
G to J). The status col contains this formula all the way down to indicate
how many months a check has not been cashed (for 500 rows) - this is from
row 3 (should be all on one line):

=IF(TYPE(D3)=2,"",IF(OR((TODAY()-D3)/30<1,D3=""),""," "&INT((TODAY()-D3)/30)))

After a cut and paste from row 3 to row 5, row 3 now is changed to:

=IF(TYPE(D5)=2,"",IF(OR((TODAY()-D5)/30<1,D5=""),""," "&INT((TODAY()-D5)/30)))

I.e. all D3's turn into D5's which is not desirable. And row 5 which
actually had contained the above formula which Excel turned row 3 into,
Excel changes to:

=IF(TYPE(#REF!)=2,"",IF(OR((TODAY()-#REF!)/30<1,#REF!=""),"","
"&INT((TODAY()-#REF!)/30)))

All D5's turn to #REF! as Excel I think tries to ensure consistent reference
to that cell, but doesn't know what to change D5 to, since it just changed
D3 to D5 in row 3. (Hope I am not confusing the heck out of anyone - but you
can easily create the sheet yourself using the formula above and fill the
col down and then do a quick cut and paste to try it out.)

Not the solution:
Originally I wanted to solve this problem by creating a custom cut and paste
functionality that the user (me) didn't even have to know about such that a
cut would really be a copy and a paste would delete the original copied
contents and paste in the desired location (i.e. copy/paste would become
copy/delete/paste) which is what I was doing manually every time I wanted a
cut and paste. I didn't want to spend the time needed to work this out and
I didn't want any macros for this sheet either so the solution was not ideal
anyway.

The solution that worked:
I changed all references that had the #REF! problem to the following:

= indirect(address(row(),4))

Which is another way of saying Dx, where x is the current row. It bloated
out the formulas but did not cause a notable change in save or update time
of the sheet. The new formula from the above example looked like this (not
pretty):

=IF(TYPE(INDIRECT(ADDRESS(ROW(),4)))=2,"",IF(OR((T ODAY()-INDIRECT(ADDRESS(ROW(),4)))/30<1,INDIRECT(ADDRESS(ROW(),4))=""),"","
"&INT((TODAY()-INDIRECT(ADDRESS(ROW(),4)))/30)))

But it works and I can cut and copy anywhere without Excel trying to change
the formula because it doesn't recognize any explicit cell references.

Regards,
Ben