Auto Completing Fields but not changing later...

[jturmel]

Here is what I'm trying to do.

I sell things online on a very small scale and I've made a spreadsheet to enter each sale that way I can track costs, inventory, profit, etc.

What I want to do is be able to enter a product code and quantity, and have all the other fields entered in automatically from a lookup table. The problem is, if I just do a normal lookup from one of the fields, then if shipping prices increase at USPS and I change the shipping value in my lookup, all my PAST sold products would have incorrect data, so it may have cost $2.00 to ship something back then but now it would change to $2.50 because I had to update my lookup table. So I'm wondering if there is a way to make a field automatically populate but then no longer populate if data in the lookup table is updated later on.

I hope this makes sense, if it doesn't I can try to explain it a little differently.

Maybe I should be doing this in Access instead... just wondering if there is a way in Excel?

Thank you,
Josh Turmel

[swatsp0p]

One way would be:

Prior to changing the data in your table, do a Copy/Paste Special>Values for all rows previously populated. Then they will no longer "lookup" in your table.

Another option:

Copy your table, make your changes in the new table and adjust the formulas to that new table for any new entries. This formula change could be fairly easy if you name your table ranges as 'Table1', 'Table2' or similiar. You could then simply highlight your desired range of formulas to update, and use Find>Replace to Find 'Table1' and replace with 'Table2'

Good Luck.