dropdown list and formulas
 

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

dropdown list and formulas
 

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

dropdown list and formulas
 

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

dropdown list and formulas
 

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

dropdown list and formulas
 

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

dropdown list and formulas
 

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