Auto Completing Fields but not changing later...
 

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


--
jturmel
------------------------------------------------------------------------
jturmel's Profile: http://www.excelforum.com/member.php...o&userid=27883
View this thread: http://www.excelforum.com/showthread...hreadid=473867

One way would be:

Prior to changing the data in your table, do a Copy/Paste
SpecialValues 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 FindReplace to Find 'Table1' and
replace with 'Table2'

Good Luck.


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=473867