I am trying to use a dropdown list to insert a formula into a cell. Each item
in the list has a different formula€¦.this being
one(=SUM(D8-(D8/0.1)/31.25)/55)
Thank you

Hi,
Go to the cell where you want the dropdown list, go to Data Validation,
list, select the range where you have your list, enter

"Georgeh" wrote:

I am trying to use a dropdown list to insert a formula into a cell. Each item
in the list has a different formula€¦.this being
one(=SUM(D8-(D8/0.1)/31.25)/55)
Thank you

First, you don't need the =sum() function.

Second, are you using data|validation?
If yes, then this won't work. You'll see the actual text of the formula.




Georgeh wrote:

I am trying to use a dropdown list to insert a formula into a cell. Each item
in the list has a different formula€¦.this being
one(=SUM(D8-(D8/0.1)/31.25)/55)
Thank you

--

Dave Peterson

Hi,
I have the dropdown list with the range of items in it. I would like to have
it so that when I select and item from that list, it will automaitcally
insert a formula into a specific cell. Each item in the list having its own
different formula.

Thanks


"Eduardo" wrote:

Hi,
Go to the cell where you want the dropdown list, go to Data Validation,
list, select the range where you have your list, enter

"Georgeh" wrote:

I am trying to use a dropdown list to insert a formula into a cell. Each item
in the list has a different formula€¦.this being
one(=SUM(D8-(D8/0.1)/31.25)/55)
Thank you

Thank you Dave,
Yes I am using data verification. Is there a methode that I can use from a
dropdown list to insert a formula that is relative to each item in the list?

"Dave Peterson" wrote:

First, you don't need the =sum() function.

Second, are you using data|validation?
If yes, then this won't work. You'll see the actual text of the formula.




Georgeh wrote:

I am trying to use a dropdown list to insert a formula into a cell. Each item
in the list has a different formula€¦.this being
one(=SUM(D8-(D8/0.1)/31.25)/55)
Thank you

--

Dave Peterson

Maybe you could use a macro that would convert the string into a formula.

If you want to try...

I'd build a table on a worksheet that shows the A1 reference style in column A
and the R1C1 reference style in column B.

Column A would be used to display the formulas and column B would be the actual
formula that was used.

In fact, you may want to "name" your formulas nicely--not in the excel sense of
naming.

Since your formula
=(D8-(D8/3.125))/55
is the same as:
(d8*17/25)/55
or
=d8*17/(25*55)
or
=(68%*d8)/55

Maybe you could use a table like:

Column A's description (in words): R1C1 Formula
(68% of column D) divided by 55 =rc4*68%/55
Column D * 5 =rc4*5
Multiply column D by 17 =rc4*17

Make sure each of the columns is formatted as text (to make data entry easier).

And you could use any expression you like in column A--just something meaningful
to the user.

Then rightclick on the worksheet tab that should have this behavior and select
view code. Then paste this into the newly opened code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim RngToInspect As Range
Dim myValidationList As Range
Dim res As Variant

Set RngToInspect = Me.Range("e2:e10")
Set myValidationList = Worksheets("sheet2").Range("myList")

If Target.Cells.Count 1 Then
Exit Sub 'one cell at a time
End If

If Intersect(Target, RngToInspect) Is Nothing Then
Exit Sub
End If

res = Application.Match(Target.Value, myValidationList, 0)

If IsError(res) Then
'no match!
'this shouldn't happen unless the target cell is empty
Else
On Error GoTo ErrHandler:
Application.EnableEvents = False
With Target
.NumberFormat = "General" 'or what you want
.FormulaR1C1 = myValidationList.Offset(res - 1, 1).Value
End With
End If

ErrHandler:
Application.EnableEvents = True

End Sub

I used a range named MyList on Sheet2 for the data|validation range.

Debra Dalgleish shows how to name that list so that you can use it on another
worksheet he
http://contextures.com/xlDataVal01.html#Name

And you could toggle between A1 reference style and R1C1 reference style by:
Tools|Options|general tab|check/uncheck R1C1 reference style box.
(xl2003 menus)

I find it easier to write the formula using A1 reference style, then convert to
R1C1 to see what it should be in the table.





Georgeh wrote:

Thank you Dave,
Yes I am using data verification. Is there a methode that I can use from a
dropdown list to insert a formula that is relative to each item in the list?

"Dave Peterson" wrote:

First, you don't need the =sum() function.

Second, are you using data|validation?
If yes, then this won't work. You'll see the actual text of the formula.




Georgeh wrote:

I am trying to use a dropdown list to insert a formula into a cell. Each item
in the list has a different formula€¦.this being
one(=SUM(D8-(D8/0.1)/31.25)/55)
Thank you

--

Dave Peterson


--

Dave Peterson