Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with paste | Excel Worksheet Functions | |||
imitating cut and paste | Excel Programming | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) | |||
Paste Problem | Excel Programming | |||
Imitating Pivot table's context awareness | Excel Programming |