How to dummy proof cut/paste from killing absolute references?

[WSBirch]

{=IFERROR("("&LEFT(VLOOKUP(VLOOKUP($B$1,IF('[PA Container Schedule.xlsm]Schedule'!$D$2:$D$5000=$B$2,'[PA Container Schedule.xlsm]Schedule'!$A$2:$E$5000,""),5,FALSE),'[PA Container Schedule.xlsm]Schedule'!$E:$J,6,FALSE),3)&") ","("&$D$2&") ")&IFERROR(VLOOKUP($B$1,IF('[PA Container Schedule.xlsm]Schedule'!$D$2:$D$5000=$B$2,'[PA Container Schedule.xlsm]Schedule'!$A$2:$E$5000,""),5,FALSE),$E$2)}

Good afternoon,
I have the above array formula running in C2. The formula is copied down from C2:C69, with absolute references from D2 and E2 respectively. The formula runs down the C column, but we have folks entering data manually into D and E columns. As an example, they'll enter the correct data into cell D6 and E6, but then immediately realize they meant to enter it into D7 and E7 and they'll immediately defer to using cut/paste from D6:E6 to D7:E7, or the like, instead of copy/paste. However, every time they cut/paste data like that, it breaks the array's absolute reference. If they cut from D6:E6 and paste to D7:E7, then the array formula in C7 throws a reference error because it loses the absolute reference to D7:E7 and the array formula in C7 looks like this:

{=IFERROR("("&LEFT(VLOOKUP(VLOOKUP($B$1,IF('[PA Container Schedule.xlsm]Schedule'!$D$2:$D$5000=$B8,'[PA Container Schedule.xlsm]Schedule'!$A$2:$E$5000,""),5,FALSE),'[PA Container Schedule.xlsm]Schedule'!$E:$J,6,FALSE),3)&") ","("&#REF!&") ")&IFERROR(VLOOKUP($B$1,IF('[PA Container Schedule.xlsm]Schedule'!$D$2:$D$5000=$B8,'[PA Container Schedule.xlsm]Schedule'!$A$2:$E$5000,""),5,FALSE),#REF!)}

Sure, I could go around and send a company wide email "Hey don't ever use cut/paste" but there's always going to be someone and I'm tired of babysitting the formulas. If the references keep getting killed even though they're absolute, what options do I have for them to continue their ways of cut/paste but also not break the formulas?

 

[rlv01]

Does your workbook have macros? One way is to use a macro to repair or 'refresh' the formulas in column C when needed.

 

[WSBirch]

It doesn't, but it could. Unless there's a better option, I might just have to do that. It would cut down on me at least manually updating each cell that gets broken.

 

[KRice]

Are you using this formula in versions of Excel prior to 365?

 

[WSBirch]

Are you using this formula in versions of Excel prior to 365?

Nope, company wide uses 365 only

 

[KRice]

Thanks...and are the values in B (except for $B$1), D, and E supposed to be relative...that is, all of them on the same row apply to the formula in C?

 

[WSBirch]

Thanks...and are the values in B (except for $B$1), D, and E supposed to be relative...that is, all of them on the same row apply to the formula in C?

Yes. All on the same row except for $B$1. Any value in D and E get applied to C. D20 and E20 are relative to C20, and so on

 

[KRice]

and in your example, B20 would also be applied to C...where this type of construction is used as a condition to find the correct row in the Schedule sheet... (Schedule!$D$2:$D$200=$B20)...I've shortened the range for debugging, but that $B20 would go with D20 and E20 for the formula in C20?

 

[WSBirch]

Yep. The only cell that actually needs to be absolute is the $B$1. "VLOOKUP($B$1". I have the formula otherwise setup to just all be absolute because I was hoping that if all of the cells are absolute, then a cut/paste wouldn't affect it, given the cell value it's looking at it absolute, but that hasn't been the case. I actually prefer if they're not all absolute references.

 

[KRice]

I suppose this is one idea worth investigating:

Prevent Only CUT and PASTE from the user Not COPY

Hi, i want to prevent the user from doing Cut and Paste but to allow him to preform Copy and paste ( when they are doing Cut and Paste their getting Ref# error in formulas) when i use this code it prevents also the Copy and Paste which is needed by user and it doesn't do the Ref error...

www.mrexcel.com