Change a lookup value but not the previous calculated entries

[kateb64]

I have a lookup table that lists the monetary cost of certain items. However, I have now been told the costs are to increase, but only items entered after 1st April should use the new price in the calculations. A simplified example would be: in March I bought 5 apples at 10 pence each = 50p. In April each apple will cost 12 pence, so the overall cost of 5 apples will be 60 pence but I want my table to show 50p for the March entry and 60p for the April entry. How can I change the individual item costs in the lookup table without making the apples I bought in March 60p? Can anyone suggest a solution? As costs may be increased a few times each year I need something I can roll on for each increase.

 

[Somendra Misra]

Hi kateb64,

See the attached file. Its a VBA solution.

Just advise if you don't want this.

Regards,

 

[kateb64]

Many thanks for your much appreciated help. I don't think this will do quite what I wanted, but that is down to my explanation! My look-up list currently has around 50 items, the prices of which can change at any point, but I am hoping it will be kept to bi-annual. The data being input can be submitted late, for example I may receive a request for payment in May that relates to items sold in March.

In my mind I think I envisaged that I would add a column to the look-up list and then amend the calculation to check the date of the invoice and select the price from the correct column?
Or that the table would only re-calculate a line of data if the invoice date was after a certain date (which could be tied to a cell)?

If the feeling is that there isn't a simple solution (I need simple!) I'm happy to find a different work-around.

 

[Somendra Misra]

Hi,

Many thanks for your much appreciated help. I don't think this will do quite what I wanted, but that is down to my explanation! My look-up list currently has around 50 items, the prices of which can change at any point, but I am hoping it will be kept to bi-annual. The data being input can be submitted late, for example I may receive a request for payment in May that relates to items sold in March.
In my mind I think I envisaged that I would add a column to the look-up list and then amend the calculation to check the date of the invoice and select the price from the correct column?
Or that the table would only re-calculate a line of data if the invoice date was after a certain date (which could be tied to a cell)?
If the feeling is that there isn't a simple solution (I need simple!) I'm happy to find a different work-around.

I did not understood what are you saying?

Try uploading a file with sample data and required result for what you want, what refrence to be considered and any other information that can be useful.

Regards,