Automatically insert a formula to a cell that is identified via user selections

[scottmcclean]

Hi there, i am trying to build a scenario tool that allows me to select a product and a year so that I can do a % increase or decrease. But the % change will need to start based on the “product” and “year” selected by the user. I have an array that contains rows of products and columns of annual sales data.

The idea is that a user selects a relevant product and the year from drop down lists which then identifies the right place in the array. Then the value in that cell has the % increase or decrease applied to it based on a % specified by the user.

I hope this makes sense?

Many thanks for any assistance

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[scottmcclean]

Hi there, apologies for the delay in replying. Here is a dropbox link - I hope it works:
Dropbox File

I am not sure how user friendly this will be - but I do not know VBA which would I think be better as the idea for this would be perhaps better suited to a more "app" type feel. Its a bit clunky as I have used Index & Match to try and locate the cell - but then I get stuck.

Thanks for taking a look

 

[mumps]

The link you provided takes me to a sign-in screen. I would need a direct link to the file.

 

[scottmcclean]

The link you provided takes me to a sign-in screen. I would need a direct link to the file.

Sorry about that - how about this: scenario.xlsx

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) <> "D8" Then Exit Sub
    Application.ScreenUpdating = False
    Dim product As Range, StYr As Range
    Range("D13") = WorksheetFunction.Sum(Range("D10:D12"))
    Set product = Range("J7:J13").Find(Range("D7"), LookIn:=xlValues, lookat:=xlWhole)
    Set StYr = Range("K6:R6").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    Range("D16") = Cells(product.Row, StYr.Column)
    Range("D17") = Range("D16") * (1 - Abs(Range("D13")))
    Application.ScreenUpdating = True
End Sub

Then delete the formulae in D13, D16, D17.  Next enter the data in D10:D12 and D14.  Next select a product in D7.  Finally, select a year in D8.  It doesn't matter the order in which you enter the data as  long as the very last thing you do is the selection of the year.  This is important because the year selection executes the macro.

 

[scottmcclean]

That's great - thank you very much!

 

[mumps]

You are very welcome.