use formula with different cells

[Misifuso]

Hi all,

I have a formula:
=IF(C7<0.000001,"Remote/Improbable",IF(C7<0.00001,"Unlikely",IF(C7<0.0001,"Possible",IF(C7<0.001,"Likely",IF(C7<0.01,"Frequent")))))

I use this formula with differents cells each time, ie, C7, C10, C12, etc..

1. Can I write the formula in another cell and each time I need to use it, I take from there and write wich cell I want to reference?
For example, I write this formula in another cell and call it by a name I defined, and after that I can use like: =MY_FORMULA(my_cell)???
2.After that, I will want to use the formula in other sheets? How can I reference to the formula from another sheet?


tanx
Misifuso

 

[pjmorris]

Firstly, welcome to the board, I hope you find what you need.

The only way I know to achieve this is to create a UserDefined Function, which is not as daunting as you might imagine, the following steps should help.

1. Press ALT+F11 or select 'Visual Basic' from the Developer Ribbon if you've got it enabled (Customise Ribbon and put a tick against Developer in right hand pane).

2. Right click the VBAProject1 in the left hand pane, from the pop up menu choose 'Insert' 'Module'. You should see 'Module 1' appear in the left hand pane and a new window on the right which is the code entry window for 'Module 1'.

3. Enter the following code in the new window:

Function MyFormula(Src As Range)
    Select Case Src
        Case Is < 0.000001
            MyFormula = "Remote/Improbable"
        Case Is < 0.00001
            MyFormula = "Unlikely"
        Case Is < 0.0001
            MyFormula = "Possible"
        Case Is < 0.001
            MyFormula = "Likely"
        Case Is < 0.01
            MyFormula = "Frequent"
        Case Else
            MyFormula = "Over 0.01"
    End Select
End Function

4. Press ALT+F11 to return to you spreadsheet.

5. When you want to use your formula simply enter =Myformula(C7) and change C7 to suit your purposes. Obviously you can amend the text thats displayed and the limits by editing the function.

Hope this helps.

Regards

 

[Misifuso]

Firstly, welcome to the board, I hope you find what you need.
.....
Hope this helps.

Regards

Wow pjmorris, it helps a lot. Now, each time a change the function it's only one change..
thank you..

Misifuso

 

[pjmorris]

my pleasure - and many thanks for the feedback, its nice to know when something works.