Cell Formula change based on another cells text

[tf37]

Is it possible to have a cell formula change based on another cells value?
I have a macro that does what I’m after, but I’d have to recreate another 128 times for each row. Can it be done where it runs a function, or on cell exit - the cell that contains the text word(s), but then copy that and just paste it into the other row cell and it knows it is now in cell M6 then M7 etc…like creating formulas and copy and pasting in another cell, it knows the range has changed...hope that makes sense - thank you gang




Macro:
Sub RateCalInOutParalegal()
'
' RateCalInOutParalegal Macro
'
Range("M5").Select
If Range("m5") = "out" Then
Range("L5") = ("=j5*40")
ElseIf Range("m5") = "in" Then
Range("L5") = ("=j5*60")
ElseIf Range("m5") = "paralegal" Then
Range("L5") = ("=j5*20")
End If


End Sub

 

[JoeMo]

Does this do what you want when entered in L5?

=IF(AND(M5<>"in",M5<>"out",M5<>"paralegal"),"",LOOKUP(M5,{"in","out","paralegal"},J5*{60,40,20}))

 

[tf37]

Yes, matter fact it works just fine - thanks
I've run into another snag though trying to automate this worksheet some, but I'll play around with it and be back with a new post if needed.
Your code sparked something I hadn't thought of...thanks again...awesome

 

[tf37]

The code sent works fine if my conditions for the columns are only for those with time out, or in, but I have some rows that include both a "in" time amount and "out" time amount.
I've use this for the "in" time column: =IF(AND(M2<>"in",M2<>"out",M2<>"paralegal"),"",LOOKUP(M2,{"in","out","paralegal"},I2*{60,40,20}))
And I've tweaked for "out" time column: =IF(AND(M2<>"in",M2<>"out",M2<>"paralegal"),"",LOOKUP(M2,{"in","out","paralegal"},J2*{40,20}))
Those seem to work ok that way for the most part, but when I trigger a column with "paralegal" it does whack on me, and I know it is due to the tweak of dropping the 60 out of the end of that code.
Is there a new method to upload a example, or the worksheet itself so I can give a better idea of what is happening?
I'll keep fiddling of course too to see if I can find a way to tweak your code to work, but think it is becoming out of my realm again in using VB or in cell formulas...
Perhaps someday I might actually learn
Thanks much