I'm working on an Excel financial template that calculates Standard Costs of Manufacturing, and on one tab I need to select from a list (Current, Increase/Decrease, NPI) to tell the calculator to use either a "standard value" or "manual entry", but coming from the same cell.
The Docs saved so that a new WB its set to 'Current' and displays the current 'List Price', 'Bulk Price', and 'Materials Cost' that are called from the 'ROI - Current' to the 'ROI - Target' tab where gross margin is calculated.
However, when you changes through the options, you need to rewrite the called values to whatever it needs to be. When you're doing process improvement, you doing this this a lot just to see how the numbers are moving. So I'm looking for a way to repopulate data a specific way every time a list option is switched.
What I'd like to accomplish is when 'Current' gets selected, the *List Price $(75), Bulk Price $(60), Materials Costs $(18), are called back from the ROI - C tab. (Preferable, nothing would be modifiable unless you changed options, like when using the Validation's error message; but not essential).
Then if Increase/Decrease is selected, List Price and Material Costs populates with the Current $ (but are modifiable fields). Here, Bulk Price is calculated as (CurTabBulk/CurTabList)xIncDecList then used to find the gross margin.
Then when 'NPI' is selected, if only List Price is modified, it calculates bulk price as 0.7 x List Price to assume Material Costs to get gross margin. So as an example of all this:
$75 List x 0.8 = $60 Wholesale X 0.3 = $18 Materials / $60 Wholesale * 100 = 70% Gross Margin (which is the NPI Market Forecast from the ROI - Current Tab building the ROI - Target Tab.)
I can code all this into the cells directly, but whenever you enter your own value, the code that exists in that cell is overwritten. So, I'm looking for a fast 'n dirty way to repopulate it a specific way every time the option is switched.
Thanks all!
CodePudding user response：
This solves it. Right click the tab to open the Code editor. Copy and past below to the window. Change Sheet1 and the Cells to what ever you need. Create a Macro button using an inserted Icon and right click it and select make macro.
Sub ResetDropDowns()
Dim rngLists As Range
Dim ListCell As Range
On Error Resume Next
Set rngLists = Sheets("Sheet1").Range("G21,H21")
On Error GoTo 0
If Not rngLists Is Nothing Then
For Each ListCell In rngLists.Cells
ListCell.Value = Range(Trim(Mid(Replace(ListCell.Validation.Formula1, ":", String(99, " ")), 2, 99))).Value
Next ListCell
End If
End Sub
CodePudding user response：
UPDATE: So I made it this far at work today using Alt-F11:
I found this code that I put into the VBA:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$4" Then
Range("H4:I7").Value = "Select"
End If
End Sub
That will set all validation boxes H4:I7 to "Select" after choosing the Category, but I want each cell to update with the value rather than having to keep selecting them since it's the only value in the list.
I then found this VBA that will reset as I need, but have no idea how to make it work at all...
Sub ResetDropDowns()
Dim rngLists As Range
Dim ListCell As Range
On Error Resume Next
Set rngLists = Sheets("Entry Sheet").UsedRange.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If Not rngLists Is Nothing Then
For Each ListCell In rngLists.Cells
ListCell.Value = Range(Trim(Mid(Replace(ListCell.Validation.Formula1, ":", String(99, " ")), 2, 99))).Value
Next ListCell
End If
End Sub
Also, After the independent Category is chosen, I will need to enter values into the white boxes under Product 1 and 2. These boxes move around depending on what ROI category is selected: Current = No white; Adj/ Price =- List & Costs, NPI = List & GM. So, I'm hoping I can just reassign the code to accomplish calling the 'dependent recalculated Values' to the correct cells as changes are made.
Follow the URL for pictures of the spread sheet. This is the dependent list code that I put into the Validation Source (the result numbers in the image) for Product 1 List Price: =INDEX($E$43:$G$43,,MATCH($G$4,$E$42:$G$42,0))
Thanks all for the help.