if statement to DISPLAY (not calculate) a formula?

  • Thread starter Thread starter Ratedr
  • Start date Start date
R

Ratedr

Is there a way to conditionally (if/then) display a formula or
something? I dont even know if thats the way to describe it..but
heres what I mean

column a column b c
a =((c1*3)+41)
100 (this is cell c1)
b =((c1*3)+4)*1.2
c =((c1*3)+4)-16
d =((c1*3)+4)*1.15
e =((c1*3)+8)

Now I have my drop down menu containing a-->e from column a above. I
have a cell already that is telling it, when the user selects "d" from
the dropdown menu, it will actually DO the formula (c1*3)+4)*1.15 and
it will display the result of 349.6....but What I want is to put
something in ANOTHER cell stating, when the dropdown menu selects the
d, it displays the formula, doesnt do the calculations, just displays
"(c1*3)+4)*1.15"
Is this possible?
 
You could use the change event to display the formula string from the
corresponding cell in another cell - for example, if you have your dropdown
in D4, then this will display the formula in cell E4:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address <> "$D$4" Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, 1).Value = "'" & Mid(Range("B:B").Cells( _
Application.Match(Target.Value, Range("A:A"), False), 1).Formula, 2)
Application.EnableEvents = True
End Sub

Copy the formula, right-click the sheet tab, select "View Code" and paste
the code into the window that appears.

HTH,
Bernie
MS Excel MVP
 
Create a 2 column table like this:

...........J.......K
1.......A......((C1*3)+41)
2.......B......((C1*3)+4)*1.2
3.......C......((C1*3)+4)-16
4.......D......((C1*3)+4)*1.15
5.......E......((C1*3)+8)

A1 = drop down

=VLOOKUP(A1,J1:K5,2,0)
 
Back
Top