Rod,
Have you looked at using the SUMPRODUCT function? With it you can use many
conditions. You would use it like this:
-SUMPRODUCT(--(Contract="SREG"),--(Second
Condition="Whatever"),--(Third="More"), Life_Premium)
As long as all of your conditions limit it to one answer you will get the
one result you want. If it results in multiple answers then it would sum
them together. I'm not sure if this work for you but you might look into it
and see if it will.
Mike
"Rod" wrote:
[Quoted Text] > After days of running into Excel limitations I have resorted to trying to do
> this in VB (definitely not my strength). I have too many nested conditionals
> for Excel to handle so I wanted to create a function in VB. I need to
> convert VLOOKUP(D50,INDIRECT(D50&"_Contract"),2,FALSE) to the VB equivalent.
> DLookup looks to be the best I could find, but one clear problem I forsee is
> DLookup is using text and my commision table is full of numbers. Most of the
> table on a different tab than the computations looks like this:
>
> Life LTC $MART H.O.M.E. G.O.O.D. M Funds
> REP 25% 10.00% 0.31% 0.31% 0.31% 30.00%
> SREP 35% 15.50% 0.36% 0.36% 0.36% 32.50%
> DIS 50% 20.00% 0.44% 0.44% 0.44% 35.00%
> DIV 60% 25.00% 0.57% 0.57% 0.57% 37.50%
> REG 70% 30.00% 0.83% 0.83% 0.83% 42.50%
> SREG 80% 35.00% 0.83% 0.83% 0.83% 47.50%
> RVP 95% 40.00% 1.23% 1.23% 1.25% 62.00%
>
>
> D51 is SREG. So, in E51 I am looking up what the Life Product compensation
> for a SREG when the customer pays an E19 premium. If E19, Life_Prem, was
> $100, then
> E51=VLOOKUP(D51,INDIRECT(D51&"_Contract"),2,FALSE)*(Life_Prem*12)*75%)*82.226%),
> where SREG_Contract is a named range from the above SREG through to 47.50%
> horizontally. SREG's commission is 80%; E51 should return 80%*100*12*75%=720
>
> Your help is VERY appreciated!
|