Cell with formula and possible custom input

0
votes

I have a cell with a formula based on another cell. This other cell is a data validation cell where the user can choose various options in a drop-down list.

As an example:

If the user chooses "A" in the drop-down list, then the formula calculates "1+1".

If the user chooses "B" in the drop-down list, the formula calcualtes "1+2".

The last option in the drop-down list is "Custom". If this option is chosen, then the user should insert a custom number.

However, if the user goes back and chooses "A" in the drop-down, the formula should be retained and calculate "1+1".

I have looked into some worksheet_change, but I have not been able to figure out how to do it.

Thanks in advance!

2
vbaexcel
assuming your cell with data validation is A2, then including an OR statement in your cell with a formula should do the trick, i.e. "IF(OR(A2="A",A2="Custom"),1+1,1+2)" – tsdn
@JK2017, I believe that your formula would not work, as the OP mentioned that if Custom is selected, it should prompt the user for a custom number and your formula would give the same result if the user chose either A or Custom... – Xabier
@Xabier - you are absolutely correct, sorry, I missed the bit where "Custom" needed user input. OK, so to keep this VBA-free, you can make cell B2 be the cell where user needs to provide value for "Custom" and then change the formula to: "=IF(A2="A",1+1,IF(A2="B",1+2,1+B2))" – tsdn

2 Answers

0
votes

If it is allowed to hardcode your answer than I suggest (w/o VBA):

=IF(A1="A";1;IF(A1="B";2;A1))+1

Even better: You have your dropdownlist in any other place and you have it done like a table:

A | 1

B | 2

Then you can have infinitely many options if you use a simply VLOOKUP like:

=IFERROR(VLOOKUP(A1;A2:B3;2;WRONG);A1)+1

This could be in cell B2. BUT in this case you have a little different behaviour as you might want.

0
votes

Using the Worksheet_Change event, you can do the following, this assumes that your Data Validation List is in cell B1 and you want the results in C1:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
Dim CustomNumber As Variant
If Target.Address = "$B$1" Then
    Select Case ws.Range("B1").Value
        Case "A"
            ws.Range("C1").Formula = "=1+1"
        Case "B"
            ws.Range("C1").Formual = "=1+2"
        Case "C"
            ws.Range("C1").Formula = "=1+3"
        Case "D"
            ws.Range("C1").Formula = "=1+4"
        Case "E"
            ws.Range("C1").Formula = "=1+5"
        Case "Custom"
            CustomNumber = InputBox("Please enter a custom number", "Custom")
            If IsNumeric(CustomNumber) Then
                ws.Range("C1").Formula = "=1+" & CustomNumber
            Else
                MsgBox "Please enter a number", vbCritical = vbOKOnly
                Exit Sub
            End If
    End Select
End If
End Sub

EDIT:

As per comments I've updated my answer to have only two Cases, either using a Custom number or using the value returned by a formula, this assumes your lookup formula is in A1, amend your code as required:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
Dim CustomNumber As Variant
If Target.Address = "$B$1" Then
    Select Case ws.Range("B1").Value
        Case "Custom"
            CustomNumber = InputBox("Please enter a custom number", "Custom")
            If IsNumeric(CustomNumber) Then
                ws.Range("C1").Formula = "=1+" & CustomNumber
            Else
                MsgBox "Please enter a number", vbCritical = vbOKOnly
                Exit Sub
            End If
        Case Else 'if your lookup formula is in A1, then the code below will add one to the value from the formula
            ws.Range("C1").Formula = "=1+" & Val(ws.Range("A1").Value)
    End Select
End If
End Sub

UPDATE:

Following OP's further comments I've updated the code to include the lookup value returned by the formula:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
Dim CustomNumber As Variant
If Target.Address = "$B$1" Then
    Select Case ws.Range("B1").Value
        Case "Custom"
            CustomNumber = InputBox("Please enter a custom number", "Custom")
            If IsNumeric(CustomNumber) Then
                ws.Range("C1").Formula = "=1+" & CustomNumber
            Else
                MsgBox "Please enter a number", vbCritical = vbOKOnly
                Exit Sub
            End If
        Case Else 'if your lookup formula is in A1, then the code below will add one to the value returned by the LookUp
            LookUpValue = "Ground type"
            LookUpTable = "Ground_type_table[#Alle]"
            ValueReturned = Application.WorksheetFunction.VLookup(LookUpValue, LookUpTable, 4, False)
            ws.Range("C1").Formula = "=1+" & Val(ValueReturned)
    End Select
End If
End Sub