-2

I currently have the following in excel for other users.

enter image description here

The Number column does not change. Users will input values in Multiplier 1 and Multiplier 2 columns and they will get a value in Outcome column.

Is there an automatically way where the user can also input a value in Outcome column and the Multiplier 1 column will change?

Currently Number and Outcome column are protected, will I need to unprotect the Outcome column?

TheEsnSiavashi
  • 1,245
  • 1
  • 14
  • 29
user8237423
  • 1
  • I am ruling out goal seek too. The reason is because I am thinking about how to allow for this functionality without the user interfacing with a message box. – user8237423 Jun 30 '17 at 17:45

2 Answers2

0

I recommend you use some VBA code using the Sub Worksheet_Change(ByVal Target as range) in the worksheet.

If the user enters a value in D2 it will put the formula "=D2/(A2*C2)" in B2 If the user enters a value in B2 it will put the formula "=A2*B2*C2" in D2

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo LastLine
If Not Intersect(Target, Range("D2")) Is Nothing Then
    Range("B2").Formula = "=D2/(A2*C2)"
ElseIf Not Intersect(Target, Range("B2")) Is Nothing Then
    Range("D2").Formula = "=A2*B2*C2"
End If
LastLine:
Application.EnableEvents = True
End Sub
Tim Joy T-Square Consulting
  • 266
  • 2
  • 12
0

This can be done. Begin by noticing that your formula is so simple that you Can easily solve for multiplier1 as a function of:

outcome divided by (number times multiplier2)

Next get tricky by adding a 1 pixel wide column that will contain your formula for outcome to the right of (now an input not a formula) the original outcome column.

Similarly add a 1 pixel wide col to the right of multiplier1 that contains your new formula for multiplier1. Adjust data validation on original input cells for multiplier1 and outcome to allow only one of the two to be entered at a time (use isblank function appropriately in an excel custom data validation rule for each cell).

Finally right align the two 1 pixel wide cells so that their calculated values appear within the corresponding enterable cells when they are blank. Yes it's tricky but it works.

Sorry this is just an outline but I have used this in an application and it works great. No vba, so undo redo don't break. Try it you'll like it.

Humbertcopperfield
  • 1