Insert cut cells without affecting formula in linked cells

[dropkickweasel]

Hi,

Column A has a list of serial numbers in a named range, "Key".
Column B has a list of products in a named range, "Product".

Column E has a list of serial numbers in a random order.
Column F has an XLOOKUP formula to return the Product based on the serial number in Column E.
The formula in F2 is: "=XLOOKUP($E$2,Key,Product)" and is copied down through Column F (with the absolute references changed to $E$3 etc).

This all works fine.

I want to track the order in which units of a product cell and offer a discount on the one that has the longest time since it last sold.
To do this, when a product sells, I want to cut the serial number from column E and paste it into cell E2, shifting all other products down by one row.
In this way, whichever product is at the bottom of the list is the one that should have a discount applied to it.

However, when I cut and insert copied cells just in column E, the formulae in column F 'shift down' to track the serial numbers they were attached to before.

So I might start off with:

But after cutting the '5' from cell E5 and inserting cut cells into cell E2, I get:

The formula in F2 has changed to reference cell E3 rather than cell E2.

This will be done with a macro, currently using the code:

Sub Reorder()

Range("E5").Cut
Range("E2").Insert Shift:=xlDown
    
End Sub

The "E5" in the macro code will change to become a variable that can be any of E2 - E6 based on userform entry (when I figure out how to do that).
How can I change the existing code so that the formulae in Column F remain unchanged?

Thanks in advance for any input.

 

[Sahak]

Hi,

In my understanding, because Cut or Move a formula, the cell references within the formula do not change no matter what type of cell reference that you use, that is why in the cote you can use column "E" & column "F", in your code add F5 & F2

Sub Reorder()
Range("E5:F5").Cut
Range("E2:F2").Insert Shift:=xlDown
End Sub

I hope it wll help.

 

[dropkickweasel]

Hi,

In my understanding, because Cut or Move a formula, the cell references within the formula do not change no matter what type of cell reference that you use, that is why in the cote you can use column "E" & column "F", in your code add F5 & F2

Sub Reorder()
Range("E5:F5").Cut
Range("E2:F2").Insert Shift:=xlDown
End Sub

I hope it wll help.

Hi,

Thanks for your response. Your change to the code works in the way you say it would.

However, I have added more information in columns C and G.
Column C is fixed information.
Column G references the information in Column C using the same XLOOKUP formula as column E, except that in cell G6 (the last row of the table), the formula has an IF condition attached to it.
I want this formula to always reference the values in Row 6.
If I implement your code changes, extending the ranges to "E5:G5" and "E2:G2", then this works fine.
But if I change the first range to "E6:G6" to change row 6, which is a possibility, then the formula with the IF condition also moves to row 2, when it should apply to the new data in row 6.

Any ideas on how to get around this?

 

[Sahak]

Hi
Sorry for the late reply.
I did not get about the IF formula in cell G6, but I have changed the range from "E5:G5" to the range "E6:G6" (see below) & in my understanding, it shows correct results.

 

[Sahak]