Changing Data in a Lookup Table

[TheScott]

Another day, another comprehension problem. I am creating, essentially, a product order form (actually a shopping list creator for my wife). Containing 'Quantity', 'Product Name' and 'Price'. When I enter the product name via a drop-down list, the price is displayed using vlookup.... no problem so far.

My problem is... or should I say, my desire is, that when the vlookup price is displayed, if I (my spouse) know the current price to be different from the one retrieved from the lookup table, I would like to post the new price and modify the data in the lookup table so that any future requests for that item will reflect the new price.

Hope this makes sense.. and Thanks in Advance for any help...

Scott

[:) Sixthsense :)]

I don't know what is your actual problem now

Because simply changing the price in the source will reflect the new price

[Saarang84]

Hi Scott,

Welcome to the forum. Can you post a workbook with sample data?

[TMS]

Surely all she has to do is change the value in the lookup table?

Regards, TMS

[TheScott]

Thank you for the reply. Not so clear on my part. My wife will be using this creation so her finding and successfully modifying the table which includes some 300 items is not what I have in mind. Ideally, I would like her to be able to enter the new price over the one posted by vlookup ( this isn't possible, I don't believe, as it erases the original vlookup formula) perhaps a second column that she could input the price change which would modify the table. This needs to be as user friendly as I can make it.

Thanks

[Saarang84]

Surely all she has to do is change the value in the lookup table?

Regards, TMS

Hi TMS,

Looks like the OP is asking for help for updation to be done on a daily basis like in a supermarket / grocery store, etc.

[TMS]

The only way I can think of is with a worksheet change event. This would monitor the column next to the VLOOKUP and, if a new value is entered, take the product code, find it in the table, and replace it with the new value. You might want to includes some checks to confirm that the change was intended.

Regards, TMS

[humdingaling]

add a new column in to be "NEW price"
assign a macro either via button or run macro to update line that has any data the new column into data table
after table is updated clear the new price column

[TheScott]

I didn't just want to leave this thread hang after folks were kind enough to respond. I went away and tried to find a solution to this problem and the more I worked on it, the more difficult the problem became. As I started considering price variations between stores, sale prices and what I had originally attempted to track, normal price changes, the amount of data required for a simple shopping list became more than the benefit was worth. Anyway, that feature dropped and prices will have to be entered manually at the time of list creation.

Thanks for the responses.. I can't consider this solved... but I can consider the responses appreciated.

Scott