SOLVED

Excel formula combinations

Brass Contributor

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

23 Replies

@kobus1305 

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.

@kobus1305 

 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?

@Subodh_Tiwari_sktneer 

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

@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.

Regards

Thank You 

@kobus1305 

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.

@Subodh_Tiwari_sktneer 

I would be so thankfull no private info just data referring to no one

https://onedrive.live.com/edit.aspx?cid=58b872a7ef66745e&page=view&resid=58B872A7EF66745E!110&parId=....

Regards

Thank You

@kobus1305 

When I opened the link you provided, all it says is this...

"This item might not exist or is no longer available"

 

You may upload the file here itself.

@Subodh_Tiwari_sktneer 

I have just opened it using the same link i have send you!!!

 

@kobus1305 

I have opened the file from the link in the post and it is there.

Hope with all of my heart you find it!!!

Regards

Thank You

@kobus1305 

Still the same issue.

Well, let's not make it more complex and as I said you may upload the file here itself.

To do so, click the link brows below the reply window and upload the file.

Refer to the following screenshot.

Upload_File.jpg

@Subodh_Tiwari_sktneer 

Hi,

You must be fedup with me by know i have copied it as you suggested.

 

Thank You so much for your efforts

Regards

@kobus1305 

 

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.

 

 

Forgot to post the code I have placed on ThisWorkbook Module.

 

Code:

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[2]="""",RC[2]=""None""),0,VLOOKUP(RC[2],'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

@Subodh_Tiwari_sktneer 

Hi, yes thank you for the code. Just a question what do i need to do to get it to work?

Regards

Thank You

@kobus1305 

When you download and open the file I uploaded, it would be opened in Protected View and you will need to click on Enable Editing when prompted.

 

Haven't you tested it yet?

@Subodh_Tiwari_sktneer 

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!!!!

Regards 

best response confirmed by kobus1305 (Brass Contributor)
Solution

@kobus1305 

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.

 

Hi,
Guest what i found what was wrong and thank you so so so much appreciate all your evert to help me. All the regards to you!!!!!
Thank You
1 best response

Accepted Solutions
best response confirmed by kobus1305 (Brass Contributor)
Solution

@kobus1305 

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.

 

View solution in original post