Forum Discussion
Excel formula combinations
Hi, I have a workbook with two worksheets. First worksheet called Prices, master list of products and prices that changes any time. Second sheet called January contains quotes, purchase orders, purchase requests or invoices. In January worksheet there is drop down lists each row its own list under Product Description column. Next to Product Description is Price column for each selected product. Price cell i use vlookup to retrieve the price of the selected product which works fine. But now when the price of a product in Prices worksheet is changed all the products in history business of all that products changes!!! How can i resolve that problem that once a product in a cell has been selected and the price retrieved that that price never changes ever??????
Regards
Thank You
Don't worry, I know that you are dealing with Macros very first time. But once you get the idea about how they work and what they can do for you, you will start learning them. 🙂
Please watch this short demo video (with no audio) to know how this works.
Let me know if doing the same on your end doesn't work for you.
23 Replies
Once VLookup formula returns a price for any product, if you copy the formula cell and paste it back as a Values, that price cell is no longer connected to the first worksheet so any change in price will not affect it. Doing this manually would be a cumbersome task so instead of using VLookup, you may use VBA to get the price of a product from the first worksheet as a value only, that would resolve your issue.
You may consider a Change Event code on second worksheet so that once you select a Product from the drop down in the product column, the corresponding price column is automatically populated with the selected product's price and you will end up having no formula in the price column. Sounds good?
Thank you so much sounds like a problem solver. Can i pick your brain some more i am not so fimiliar with exactly what Change Event code consists of and the implementation thereof.
Regards
VBA solution would be specific to the layout of your data on both the sheets.
If you share your file, I can build a solution for you.
If your file contains private data, save your file to either One Drive or Google Drive and share the link with me in a private message.
OR share a sample file after removing any confidential data which has the layout as same as of your original file, I will build the solution in the sample file and let you know how you can implement the solution in your original file and how to tweak the code if required.
Afraid that doesn't work such way, Excel doesn't keep the history. As variant you may complicate the logic a bit keeping not only the price for the product, but also dates when the new price was assigned. With that you may check in which date range the invoice is and pick-up the proper price based both on product and date.
SergeiBaklan Thank you for your response. Actual fact i only want to keep the price on the January worksheet for each selected product price either quotes, purchase orders, purchase requests or invoices static the date of each business is linked from another workbook like Date, Validity date, Company, Responsible person, Company Address, Cell Phone and Responsible Person Completing the form.
Regards
Thank You
