Hi everyone, I touched a similar topic on May 11th I on this forum and today I'd like to extend it a little.

Need a drop-down list consisting of only two elements whereas the first one called e.g. "FORMULA" would calculate the result acc. to the given formula and the second one called "OTHER" would enable me to put in the value from the keyboard.Could somebody help me please?

Thanks a lot!!Zennon

I believe we showed you how to deal with the "Other" requirement earlier this week. Please explain the "Formula" part of this request in more detail.

What exactly are you trying to do?

Hi DerbyDad03! I'm not sure if it's the same issue : before I wanted to have in e.g. A1 a standard list of some fixed values to choose and additional "other" value to put in from the keyboard.

Now, I try to have a bit different situation because the values are chosen in another cell .in A1 and in B1 I have a multiple IF formula that basing on values set in A1 calculates the result:, like this: =IF(A1=0;0;IF(A1<70;14;IF(A1<130;16;IF(A1<240;18;IF(A1<300;24;33))))Now, I intend to have in B1 the choice between "stiff" formula and free input value from keyboard, so the drop-down list having two items "FORMULA" and "OTHER".

Choosing FORMULA the value in the cell comes from the above formula and choosin OTHER I could put any value from the keyboard.I do realise that this is a bit entangled but maybe you could help me doing something like this or similar...

Thanks a lot in advance!!

Zennon

You can't have the IF function and a Drop Down in the same cell. You can however, have the IF function anywhere else and put it the result in the cell based on the Drop Down choice.

All you need to do is expand the code I offered earlier this week:

Assuming the Drop Down is in B1, put your IF function in C1, and use this code:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$1" Then 'If Drop Down choice is Other, get value 'from user and place it in B1 If Target = "Other" Then otherVal = Application.InputBox("Enter Other Value") 'Quit if user Cancels If otherVal = False Then Exit Sub Target = otherVal End If 'If Drop Down choice is Formula, get value 'from C1 and place it in B1 If Target = "Formula" Then Target = Range("C1") End If End If End Sub

Thank you Derby Dad03!

Pleasure to always get such a concrete answer... :-)

BRZennon

Ask Your Question

Weekly Poll

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History