Udf

[drl44]

Hi
I am using this formula over and over and changing the A1 cell references is there an easier way to do this.
Here is the formula
=RANDBETWEEN(LEFT(A1,FIND("d",A1)-1),RIGHT(A1,LEN(A1)-FIND("d",A1))*LEFT(A1,FIND("d",A1)-1))
I think a UDF is the answer I’m looking for but do not have any experience with UDF
Any help would be appreciated

 

[mikerickson]

A couple of ways, depending on when you are changing the A1.
1) Use a named Range. Define a name myCell RefersTo: =Sheet1!$A$1

Then the formula =RANDBETWEEN(LEFT(myCell, FIND("d",myCell)-1), RIGHT(myCell,LEN(myCell)-FIND("d",myCell))*LEFT(myCell,FIND("d",myCell)-1))

Changing the definition of myCell will effect all of the cells with the formula.
If it appears in multiple places in the spreadsheet, relative referencing in the definition of myCell might be appropriate.

Under what circumstances are you changing A1?
Will Excel's relative/absolute addressing meet your need, so that copy/paste or fillDown or other cell copying methods can be used?