dropkickweasel
Board Regular
- Joined
- Feb 2, 2014
- Messages
- 70
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:
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.
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:
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.