VLookup Question - If i update the lookup table i do not want to update old data

[mrabattoir]

I have a "client sales" sheet and a "products" sheet.
When a client buys a product i use a drop down box on the "client sales" sheet that looks up products on the "products" sheet and it returns the value of the product.

After a couple of months the product price may increase, so i have to update the cost of the item on the "products" sheet

PROBLEM
All previous sales automatically update after i update the products price on the "products" sheet.
Is there a way of stopping all old data updating and only new lookups now use the new price?

[zbor]

Since you already need to enter new values maybe you could enter also date when new value occures.
That way you would also have history of price changing.
And you could achieve above criteria by two criteria: date and product.

If you don't want to do that you would need VBA.

[sktneer]

You need to use VBA code to insert formula in client sheet if you don't want the earlier records to be updated with any change in the products sheet in future.
Please upload a sample workbook exactly same as your original workbook with enough sample data in it along with the formula you are currently using.

[mrabattoir]

Hi sktneer. I have attached an example of the sheet i am using.

The client sheet is named as "Joe Bloggs" and the lookup data is on the "Products" sheet.

1. I select the sheet "Joe Bloggs" and under the chart there is a sales section.
2. I double click the date column to enter a date.
3. In the item column there is a drop down to select the item.

The Vlookup returns the data as expected but if i update the cost/fuel/profit on the product sheet i do not want it to update data already entered into the client sheet.

Thanks in advance
Kevin

[sktneer]

Please find the attached sheet and follow the instructions in red text i.e. start entering sales figure in row 23. First enter date in U23, then select item from V23 drop down list, as soon as you select the item from the drop down list, the remaining cells in that row will be automatically filled with the correct value.
Now go to the products sheet and make a change in the cost for the item you just entered in the row 23 of Client Name sheet.
Now come back to Client Name sheet and enter a new record for the same item in row 24 and see if the new row of records contains the updated cost for that item and the row 23 still contains the old cost for that item.

For detail see the attached sheet. The VBA code in on Client Name sheet. To view the code right click on Client Name sheet -->View code.

[mrabattoir]

Thats cool sktneer.
Thanks for the speedy response.

Just playing with the sheet now and trying to work out your vba

I did get it to work incorrectly but not sure what i did lol

[sktneer]

Take your own time to give it a try and make sure that you are getting the correct values.

[mrabattoir]

Hello, i have re-created the problem i mentioned above.

I have attached another file that has two sales in the client sheet.
If i edit the last sale or add another sale, all works ok
If i try to edit the top item (not the last one) in the list, the code doesn't work.

Regards
Kevin

PS did you have a datepicker on the sheet?
Only other bit i didn't understand was "target" references

[sktneer]

Earlier the code was designed to update the value of last row entered. It was assumed that you will not edit previous values once entered in the sales table.
But if you need to edit the previous records as well, please find the attached sheet to see if this works as per your requirement now.

[mrabattoir]

Earlier the code was designed to update the value of last row entered. It was assumed that you will not edit previous values once entered in the sales table.
But if you need to edit the previous records as well, please find the attached sheet to see if this works as per your requirement now.

Perfect. You sir are a genius

Thank you.

[sktneer]

Glad to help you. Thanks for the kind words.
If that takes care of your question, please mark your thread as solved by selecting Thread Tools --> Mark thread as solved.