Drop-down list with formula in Excel

Asus Eee pc 1005ha blue netbook
May 14, 2010 at 00:59:35
Specs: Windows XP Pro, 2096
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!!


See More: Drop-down list with formula in Excel

May 14, 2010 at 11:40:37
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?

Report •

May 14, 2010 at 12:50:49
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!!


Report •

May 14, 2010 at 16:12:07
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

Report •

Related Solutions

May 14, 2010 at 21:39:31
Thank you Derby Dad03!
Pleasure to always get such a concrete answer... :-)



Report •

Ask Question