Oct 09 2020 09:01 AM
Oct 09 2020 09:01 AM
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??????
Oct 09 2020 09:31 AM
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.
Oct 09 2020 09:50 AM
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?
Oct 09 2020 10:20 AM
@Sergei Baklan 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.
Oct 09 2020 10:55 AM
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.
Oct 09 2020 11:49 AM
I would be so thankfull no private info just data referring to no one
Oct 09 2020 03:44 PM
Hi i saved it again lets try this time!!!
Oct 10 2020 05:57 AM
Sorry for the late reply as I was busy in a project.
Please find the attached with the Sheet Change Event code placed on ThisWorkbook Module.
To test the code, go to any Month Sheet and select a Product from any Product Description Column and the Unit Price will be automatically populated in the relevant cell.
If you change the price of an existing product on Product List Sheet, the already existing Unit Prices for that product on any month sheet will not be changed.
But if you wish to update the existing old prices for that item on Month Sheets for some reason, all you need to do is, delete the selected Product and the corresponding Old Price would also be deleted and if you select the Product again from the drop down. the new price would be reflected in the corresponding Unit Price column.
Please let me know if that works as desired on your end.
Oct 10 2020 06:00 AM
Forgot to post the code I have placed on ThisWorkbook Module.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub Dim r As Long Dim c As Long r = Target.Row c = Target.Column On Error GoTo Skip If VBA.Trim(LCase(Sh.Cells(2, c).Value)) = "product description" Then If Target <> "" Then If Target.Offset(0, -2) = "" Then Application.EnableEvents = False With Target.Offset(0, -2) .FormulaR1C1 = "=IF(OR(RC="""",RC=""None""),0,VLOOKUP(RC,'Product List'!R3C1:R20C3,3,FALSE))" .Value = .Value End With End If Else Target.Offset(0, -2) = "" End If End If Skip: Application.EnableEvents = True End Sub
Oct 11 2020 03:23 AM
Hi, Yes i have disabled protected view and enabled macros but if i click on a product description cell the price do not change. Sorry for being a nuisance to you i have used Visual Studio 6 extensively developed large management systems for two companies but never used VBA for Excel before so i do not know how the two, Excel and VBA, fits together and what to do.
Thank You so much!!!!
Oct 11 2020 05:45 AMSolution
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.
Oct 11 2020 07:41 AM