I
I need help please
** Using Excel 2003 **
There was a help page that said go to VB editor, make a new module, and a
function - then call that function within the workbook. So I made a new
module and typed:
Function Commission(MyNumber)
Commission = MyNumber * 0.6
End Function
In that workbook, I did =commission(D7) and =commission(100) and both gave
me the #name error. I followed the example... cant find out how I went wrong.
Anyway - this is what I'm looking for. I have MANY LONG functions, and was
hoping to do something like this:
(note that I have named data ranges - and DateCell & SiteCell differ - I've
got this formula set up for over 500 cells with different DateCell & SiteCell
on each but now I need to add more usability to the worksheet!!)
Current: = SUMPRODUCT( dates1 = DateCell ) * ( sites1 = SiteCell ) *
referred1 ) + SUMPRODUCT ( dates2 = DateCell ) * ( sites2 = SiteCell ) *
referred2 ) + SUMPRODUCT ( dates3 = DateCell ) * ( sites3 = SiteCell ) *
referred3 ) + SUMPRODUCT ( dates4 = DateCell ) * ( sites4 = SiteCell ) *
referred4 )
What I want to type in the cell is = FindReferred(DateCell, SiteCell)
and have a custom function like:
Function FindReferred(DateCell, SiteCell)
FindReferred = (scary long formula above)
End Function
So -
1) how do I get the workbook to find the function I made (I was having the
#name error with the super simple custom function I made)
2) can I use current excel functions like SUMPRODUCT in a custom defined
function?
3) can I have multiple parameters (I want to include 2 cells to make
calculations from - DateCell & SiteCell)
There was a help page that said go to VB editor, make a new module, and a
function - then call that function within the workbook. So I made a new
module and typed:
Function Commission(MyNumber)
Commission = MyNumber * 0.6
End Function
In that workbook, I did =commission(D7) and =commission(100) and both gave
me the #name error. I followed the example... cant find out how I went wrong.
Anyway - this is what I'm looking for. I have MANY LONG functions, and was
hoping to do something like this:
(note that I have named data ranges - and DateCell & SiteCell differ - I've
got this formula set up for over 500 cells with different DateCell & SiteCell
on each but now I need to add more usability to the worksheet!!)
Current: = SUMPRODUCT( dates1 = DateCell ) * ( sites1 = SiteCell ) *
referred1 ) + SUMPRODUCT ( dates2 = DateCell ) * ( sites2 = SiteCell ) *
referred2 ) + SUMPRODUCT ( dates3 = DateCell ) * ( sites3 = SiteCell ) *
referred3 ) + SUMPRODUCT ( dates4 = DateCell ) * ( sites4 = SiteCell ) *
referred4 )
What I want to type in the cell is = FindReferred(DateCell, SiteCell)
and have a custom function like:
Function FindReferred(DateCell, SiteCell)
FindReferred = (scary long formula above)
End Function
So -
1) how do I get the workbook to find the function I made (I was having the
#name error with the super simple custom function I made)
2) can I use current excel functions like SUMPRODUCT in a custom defined
function?
3) can I have multiple parameters (I want to include 2 cells to make
calculations from - DateCell & SiteCell)