I have a number of data validation fields which depending on their value,
produces a second tier of dv's.

Now the problem I am having is one cell in particular has over 9 entires and
Excel will only allow me to input 7 IF statements (I have looked at the
Contextures site and tried using their alternatives/suggestions to no avail
however the IF statements have worked with smaller entries elsewhere).

So I was thinking, could I not write the IF statement in VB and get the cell
(say A1) to run it after its updated (which will then use the correct dv for
cell A2 depending on A1'a value). How would I get the cell to call the
fucntion (if possible) please?

Kind regards,
Mav

You are asking for a UDF macro. The UDF gets update just like any other
worksheet function and gets called just like every other worksheet function.


=newfunction(A1, B2:B7, "hello",7)


function newfunction(Target1 as Range, Target2 as Range, NewString as
string, Strikes as Integer)

if NewString = "hello" and Strikes <= 6 then
newfunction = True
else
newfunction = False
end if

end function


"Maver1ck666" wrote:

> I have a number of data validation fields which depending on their value,
> produces a second tier of dv's.
>
> Now the problem I am having is one cell in particular has over 9 entires and
> Excel will only allow me to input 7 IF statements (I have looked at the
> Contextures site and tried using their alternatives/suggestions to no avail
> however the IF statements have worked with smaller entries elsewhere).
>
> So I was thinking, could I not write the IF statement in VB and get the cell
> (say A1) to run it after its updated (which will then use the correct dv for
> cell A2 depending on A1'a value). How would I get the cell to call the
> fucntion (if possible) please?
>
> Kind regards,
> Mav

Hi Joel

Thanks for the fast reply. I don't understand what you mean though. Any
chance you could put it simply (or step by step) for me please (has been a
long day already!).

Thanks again!
Mav

"Joel" wrote:

> You are asking for a UDF macro. The UDF gets update just like any other
> worksheet function and gets called just like every other worksheet function.
>
>
> =newfunction(A1, B2:B7, "hello",7)
>
>
> function newfunction(Target1 as Range, Target2 as Range, NewString as
> string, Strikes as Integer)
>
> if NewString = "hello" and Strikes <= 6 then
> newfunction = True
> else
> newfunction = False
> end if
>
> end function
>
>
> "Maver1ck666" wrote:
>
> > I have a number of data validation fields which depending on their value,
> > produces a second tier of dv's.
> >
> > Now the problem I am having is one cell in particular has over 9 entires and
> > Excel will only allow me to input 7 IF statements (I have looked at the
> > Contextures site and tried using their alternatives/suggestions to no avail
> > however the IF statements have worked with smaller entries elsewhere).
> >
> > So I was thinking, could I not write the IF statement in VB and get the cell
> > (say A1) to run it after its updated (which will then use the correct dv for
> > cell A2 depending on A1'a value). How would I get the cell to call the
> > fucntion (if possible) please?
> >
> > Kind regards,
> > Mav

If you post the worksheet function I could convert it to UDF code.

The UDF gets entered into a worksheet like any other excel spreadsheret
function. functtions return one value such as True, a number, a string.

to create a UDF do the following
1) On worksheet menu Tools - Macro - Security
2) check to see if the security level is low or medium. If not set to medium
3) On worksheet menu Tools - Macro - Visual Basic Editor, normally refered
to as VBA
4) From VBA menu Insert - Module
5) Create a function like the one I posted

the first line is : function function_name(parameter list)
last line is : end function
the returned value must be set someplace in the code assigning the value to
the functtion name like

function_name = 123


"Maver1ck666" wrote:

> Hi Joel
>
> Thanks for the fast reply. I don't understand what you mean though. Any
> chance you could put it simply (or step by step) for me please (has been a
> long day already!).
>
> Thanks again!
> Mav
>
> "Joel" wrote:
>
> > You are asking for a UDF macro. The UDF gets update just like any other
> > worksheet function and gets called just like every other worksheet function.
> >
> >
> > =newfunction(A1, B2:B7, "hello",7)
> >
> >
> > function newfunction(Target1 as Range, Target2 as Range, NewString as
> > string, Strikes as Integer)
> >
> > if NewString = "hello" and Strikes <= 6 then
> > newfunction = True
> > else
> > newfunction = False
> > end if
> >
> > end function
> >
> >
> > "Maver1ck666" wrote:
> >
> > > I have a number of data validation fields which depending on their value,
> > > produces a second tier of dv's.
> > >
> > > Now the problem I am having is one cell in particular has over 9 entires and
> > > Excel will only allow me to input 7 IF statements (I have looked at the
> > > Contextures site and tried using their alternatives/suggestions to no avail
> > > however the IF statements have worked with smaller entries elsewhere).
> > >
> > > So I was thinking, could I not write the IF statement in VB and get the cell
> > > (say A1) to run it after its updated (which will then use the correct dv for
> > > cell A2 depending on A1'a value). How would I get the cell to call the
> > > fucntion (if possible) please?
> > >
> > > Kind regards,
> > > Mav

The closet I have got to any code is what I was going to use in the dv screen
(but was too long) was this:

=IF(C16="E_Commerce",(E_Commerce),IF(C16="FA_Admin",(FA_Admin),IF(C16="Fund",(Fund),IF(C16="Investment_Admin",(Investment_Admin),IF(C16="Money_Out",(Money_Out),IF(C16="New_Business",(New_Business),IF(C16="Product",(Product),IF(C16="Servicing",(Servicing),IF(C16="Switches_Redirections",(Switches_Redirections))))))))))

Im still confused on how C16 would run the module after it has been updated.

Really sorry for being thick here.

Mav

P.S. The code above was being used for cell C17.

"Joel" wrote:

> If you post the worksheet function I could convert it to UDF code.
>
> The UDF gets entered into a worksheet like any other excel spreadsheret
> function. functtions return one value such as True, a number, a string.
>
> to create a UDF do the following
> 1) On worksheet menu Tools - Macro - Security
> 2) check to see if the security level is low or medium. If not set to medium
> 3) On worksheet menu Tools - Macro - Visual Basic Editor, normally refered
> to as VBA
> 4) From VBA menu Insert - Module
> 5) Create a function like the one I posted
>
> the first line is : function function_name(parameter list)
> last line is : end function
> the returned value must be set someplace in the code assigning the value to
> the functtion name like
>
> function_name = 123
>
>
> "Maver1ck666" wrote:
>
> > Hi Joel
> >
> > Thanks for the fast reply. I don't understand what you mean though. Any
> > chance you could put it simply (or step by step) for me please (has been a
> > long day already!).
> >
> > Thanks again!
> > Mav
> >
> > "Joel" wrote:
> >
> > > You are asking for a UDF macro. The UDF gets update just like any other
> > > worksheet function and gets called just like every other worksheet function.
> > >
> > >
> > > =newfunction(A1, B2:B7, "hello",7)
> > >
> > >
> > > function newfunction(Target1 as Range, Target2 as Range, NewString as
> > > string, Strikes as Integer)
> > >
> > > if NewString = "hello" and Strikes <= 6 then
> > > newfunction = True
> > > else
> > > newfunction = False
> > > end if
> > >
> > > end function
> > >
> > >
> > > "Maver1ck666" wrote:
> > >
> > > > I have a number of data validation fields which depending on their value,
> > > > produces a second tier of dv's.
> > > >
> > > > Now the problem I am having is one cell in particular has over 9 entires and
> > > > Excel will only allow me to input 7 IF statements (I have looked at the
> > > > Contextures site and tried using their alternatives/suggestions to no avail
> > > > however the IF statements have worked with smaller entries elsewhere).
> > > >
> > > > So I was thinking, could I not write the IF statement in VB and get the cell
> > > > (say A1) to run it after its updated (which will then use the correct dv for
> > > > cell A2 depending on A1'a value). How would I get the cell to call the
> > > > fucntion (if possible) please?
> > > >
> > > > Kind regards,
> > > > Mav

It looks like you are using named ranges. Is this right? Belwo is the code.
I used a Select Case instead of an If statement. You could of used a Match
function in the worksheet if the length of the function is less than 256
characters.

call with following
=GetInvestmentName(C16)


Function GetInvestmentName(InvestmentName As String)

Select Case InvestmentName
Case "New_Business"
GetInvestmentName = Range("New_Business")
Case "Money_Out"
GetInvestmentName = Range("Money_Out")
Case "Investment_Admin"
GetInvestmentName = Range("Investment_Admin")
Case "Fund"
GetInvestmentName = Range("Fund")
Case "FA_Admin"
GetInvestmentName = Range("FA_Admin")
Case "E_Commerce"
GetInvestmentName = Range("E_Commerce")
Case "Switches_Redirections"
GetInvestmentName = Range("Switches_Redirections")
Case "Servicing"
GetInvestmentName = Range("Servicing")
Case "Product"
GetInvestmentName = Range("Product")

End Select

End Function


"Maver1ck666" wrote:

> The closet I have got to any code is what I was going to use in the dv screen
> (but was too long) was this:
>
> =IF(C16="E_Commerce",(E_Commerce),IF(C16="FA_Admin",(FA_Admin),IF(C16="Fund",(Fund),IF(C16="Investment_Admin",(Investment_Admin),IF(C16="Money_Out",(Money_Out),IF(C16="New_Business",(New_Business),IF(C16="Product",(Product),IF(C16="Servicing",(Servicing),IF(C16="Switches_Redirections",(Switches_Redirections))))))))))
>
> Im still confused on how C16 would run the module after it has been updated.
>
> Really sorry for being thick here.
>
> Mav
>
> P.S. The code above was being used for cell C17.
>
> "Joel" wrote:
>
> > If you post the worksheet function I could convert it to UDF code.
> >
> > The UDF gets entered into a worksheet like any other excel spreadsheret
> > function. functtions return one value such as True, a number, a string.
> >
> > to create a UDF do the following
> > 1) On worksheet menu Tools - Macro - Security
> > 2) check to see if the security level is low or medium. If not set to medium
> > 3) On worksheet menu Tools - Macro - Visual Basic Editor, normally refered
> > to as VBA
> > 4) From VBA menu Insert - Module
> > 5) Create a function like the one I posted
> >
> > the first line is : function function_name(parameter list)
> > last line is : end function
> > the returned value must be set someplace in the code assigning the value to
> > the functtion name like
> >
> > function_name = 123
> >
> >
> > "Maver1ck666" wrote:
> >
> > > Hi Joel
> > >
> > > Thanks for the fast reply. I don't understand what you mean though. Any
> > > chance you could put it simply (or step by step) for me please (has been a
> > > long day already!).
> > >
> > > Thanks again!
> > > Mav
> > >
> > > "Joel" wrote:
> > >
> > > > You are asking for a UDF macro. The UDF gets update just like any other
> > > > worksheet function and gets called just like every other worksheet function.
> > > >
> > > >
> > > > =newfunction(A1, B2:B7, "hello",7)
> > > >
> > > >
> > > > function newfunction(Target1 as Range, Target2 as Range, NewString as
> > > > string, Strikes as Integer)
> > > >
> > > > if NewString = "hello" and Strikes <= 6 then
> > > > newfunction = True
> > > > else
> > > > newfunction = False
> > > > end if
> > > >
> > > > end function
> > > >
> > > >
> > > > "Maver1ck666" wrote:
> > > >
> > > > > I have a number of data validation fields which depending on their value,
> > > > > produces a second tier of dv's.
> > > > >
> > > > > Now the problem I am having is one cell in particular has over 9 entires and
> > > > > Excel will only allow me to input 7 IF statements (I have looked at the
> > > > > Contextures site and tried using their alternatives/suggestions to no avail
> > > > > however the IF statements have worked with smaller entries elsewhere).
> > > > >
> > > > > So I was thinking, could I not write the IF statement in VB and get the cell
> > > > > (say A1) to run it after its updated (which will then use the correct dv for
> > > > > cell A2 depending on A1'a value). How would I get the cell to call the
> > > > > fucntion (if possible) please?
> > > > >
> > > > > Kind regards,
> > > > > Mav

I don't think you need an if statement. try Indirect

=indirect(C16)



"Maver1ck666" wrote:

> The closet I have got to any code is what I was going to use in the dv screen
> (but was too long) was this:
>
> =IF(C16="E_Commerce",(E_Commerce),IF(C16="FA_Admin",(FA_Admin),IF(C16="Fund",(Fund),IF(C16="Investment_Admin",(Investment_Admin),IF(C16="Money_Out",(Money_Out),IF(C16="New_Business",(New_Business),IF(C16="Product",(Product),IF(C16="Servicing",(Servicing),IF(C16="Switches_Redirections",(Switches_Redirections))))))))))
>
> Im still confused on how C16 would run the module after it has been updated.
>
> Really sorry for being thick here.
>
> Mav
>
> P.S. The code above was being used for cell C17.
>
> "Joel" wrote:
>
> > If you post the worksheet function I could convert it to UDF code.
> >
> > The UDF gets entered into a worksheet like any other excel spreadsheret
> > function. functtions return one value such as True, a number, a string.
> >
> > to create a UDF do the following
> > 1) On worksheet menu Tools - Macro - Security
> > 2) check to see if the security level is low or medium. If not set to medium
> > 3) On worksheet menu Tools - Macro - Visual Basic Editor, normally refered
> > to as VBA
> > 4) From VBA menu Insert - Module
> > 5) Create a function like the one I posted
> >
> > the first line is : function function_name(parameter list)
> > last line is : end function
> > the returned value must be set someplace in the code assigning the value to
> > the functtion name like
> >
> > function_name = 123
> >
> >
> > "Maver1ck666" wrote:
> >
> > > Hi Joel
> > >
> > > Thanks for the fast reply. I don't understand what you mean though. Any
> > > chance you could put it simply (or step by step) for me please (has been a
> > > long day already!).
> > >
> > > Thanks again!
> > > Mav
> > >
> > > "Joel" wrote:
> > >
> > > > You are asking for a UDF macro. The UDF gets update just like any other
> > > > worksheet function and gets called just like every other worksheet function.
> > > >
> > > >
> > > > =newfunction(A1, B2:B7, "hello",7)
> > > >
> > > >
> > > > function newfunction(Target1 as Range, Target2 as Range, NewString as
> > > > string, Strikes as Integer)
> > > >
> > > > if NewString = "hello" and Strikes <= 6 then
> > > > newfunction = True
> > > > else
> > > > newfunction = False
> > > > end if
> > > >
> > > > end function
> > > >
> > > >
> > > > "Maver1ck666" wrote:
> > > >
> > > > > I have a number of data validation fields which depending on their value,
> > > > > produces a second tier of dv's.
> > > > >
> > > > > Now the problem I am having is one cell in particular has over 9 entires and
> > > > > Excel will only allow me to input 7 IF statements (I have looked at the
> > > > > Contextures site and tried using their alternatives/suggestions to no avail
> > > > > however the IF statements have worked with smaller entries elsewhere).
> > > > >
> > > > > So I was thinking, could I not write the IF statement in VB and get the cell
> > > > > (say A1) to run it after its updated (which will then use the correct dv for
> > > > > cell A2 depending on A1'a value). How would I get the cell to call the
> > > > > fucntion (if possible) please?
> > > > >
> > > > > Kind regards,
> > > > > Mav

Yes I am using named ranges with OFFSET.

I did try using INDIRECT but it didn't work (it wouldn't produce any results
in the dv field). Think I read somewhere it's because of the offset formula
and that the 2 aren' compatable.

Thanks for the other code but I cant seem to get Excel to call it. Am I
meant ot be adding the =GetInvestmentName(C16) to the dv wizard/screen, as a
formula???

"Joel" wrote:

> I don't think you need an if statement. try Indirect
>
> =indirect(C16)
>
>
>
> "Maver1ck666" wrote:
>
> > The closet I have got to any code is what I was going to use in the dv screen
> > (but was too long) was this:
> >
> > =IF(C16="E_Commerce",(E_Commerce),IF(C16="FA_Admin",(FA_Admin),IF(C16="Fund",(Fund),IF(C16="Investment_Admin",(Investment_Admin),IF(C16="Money_Out",(Money_Out),IF(C16="New_Business",(New_Business),IF(C16="Product",(Product),IF(C16="Servicing",(Servicing),IF(C16="Switches_Redirections",(Switches_Redirections))))))))))
> >
> > Im still confused on how C16 would run the module after it has been updated.
> >
> > Really sorry for being thick here.
> >
> > Mav
> >
> > P.S. The code above was being used for cell C17.
> >
> > "Joel" wrote:
> >
> > > If you post the worksheet function I could convert it to UDF code.
> > >
> > > The UDF gets entered into a worksheet like any other excel spreadsheret
> > > function. functtions return one value such as True, a number, a string.
> > >
> > > to create a UDF do the following
> > > 1) On worksheet menu Tools - Macro - Security
> > > 2) check to see if the security level is low or medium. If not set to medium
> > > 3) On worksheet menu Tools - Macro - Visual Basic Editor, normally refered
> > > to as VBA
> > > 4) From VBA menu Insert - Module
> > > 5) Create a function like the one I posted
> > >
> > > the first line is : function function_name(parameter list)
> > > last line is : end function
> > > the returned value must be set someplace in the code assigning the value to
> > > the functtion name like
> > >
> > > function_name = 123
> > >
> > >
> > > "Maver1ck666" wrote:
> > >
> > > > Hi Joel
> > > >
> > > > Thanks for the fast reply. I don't understand what you mean though. Any
> > > > chance you could put it simply (or step by step) for me please (has been a
> > > > long day already!).
> > > >
> > > > Thanks again!
> > > > Mav
> > > >
> > > > "Joel" wrote:
> > > >
> > > > > You are asking for a UDF macro. The UDF gets update just like any other
> > > > > worksheet function and gets called just like every other worksheet function.
> > > > >
> > > > >
> > > > > =newfunction(A1, B2:B7, "hello",7)
> > > > >
> > > > >
> > > > > function newfunction(Target1 as Range, Target2 as Range, NewString as
> > > > > string, Strikes as Integer)
> > > > >
> > > > > if NewString = "hello" and Strikes <= 6 then
> > > > > newfunction = True
> > > > > else
> > > > > newfunction = False
> > > > > end if
> > > > >
> > > > > end function
> > > > >
> > > > >
> > > > > "Maver1ck666" wrote:
> > > > >
> > > > > > I have a number of data validation fields which depending on their value,
> > > > > > produces a second tier of dv's.
> > > > > >
> > > > > > Now the problem I am having is one cell in particular has over 9 entires and
> > > > > > Excel will only allow me to input 7 IF statements (I have looked at the
> > > > > > Contextures site and tried using their alternatives/suggestions to no avail
> > > > > > however the IF statements have worked with smaller entries elsewhere).
> > > > > >
> > > > > > So I was thinking, could I not write the IF statement in VB and get the cell
> > > > > > (say A1) to run it after its updated (which will then use the correct dv for
> > > > > > cell A2 depending on A1'a value). How would I get the cell to call the
> > > > > > fucntion (if possible) please?
> > > > > >
> > > > > > Kind regards,
> > > > > > Mav

You just may need to force a change in the cell for it to work.

First try simply to select the cell and then in the Fx box at the top of the
worksheet click the end of the line and then hit Enter. this will force the
functtion to re-execute.

second try putting a break point in the VBA function at the 1st line by
click the first line with the mouse and pressing F9. Go back and force and
change to the worksheet. Then when it hits the break point press F8 to step
through the code. It should step right through to the END FUNCTION. If it
fails at the
GetInvestmentName = Range("FA_Admin")

then there is something wrong with the Define Name in the worksheet. I
suspect this is the case because the INDIRECT() isn't working.

check Inset - Name - Define and make sure the Referto item doesn't havve any
quotes. It should look like the line below
=Sheet1!$D$11

"Maver1ck666" wrote:

> Yes I am using named ranges with OFFSET.
>
> I did try using INDIRECT but it didn't work (it wouldn't produce any results
> in the dv field). Think I read somewhere it's because of the offset formula
> and that the 2 aren' compatable.
>
> Thanks for the other code but I cant seem to get Excel to call it. Am I
> meant ot be adding the =GetInvestmentName(C16) to the dv wizard/screen, as a
> formula???
>
> "Joel" wrote:
>
> > I don't think you need an if statement. try Indirect
> >
> > =indirect(C16)
> >
> >
> >
> > "Maver1ck666" wrote:
> >
> > > The closet I have got to any code is what I was going to use in the dv screen
> > > (but was too long) was this:
> > >
> > > =IF(C16="E_Commerce",(E_Commerce),IF(C16="FA_Admin",(FA_Admin),IF(C16="Fund",(Fund),IF(C16="Investment_Admin",(Investment_Admin),IF(C16="Money_Out",(Money_Out),IF(C16="New_Business",(New_Business),IF(C16="Product",(Product),IF(C16="Servicing",(Servicing),IF(C16="Switches_Redirections",(Switches_Redirections))))))))))
> > >
> > > Im still confused on how C16 would run the module after it has been updated.
> > >
> > > Really sorry for being thick here.
> > >
> > > Mav
> > >
> > > P.S. The code above was being used for cell C17.
> > >
> > > "Joel" wrote:
> > >
> > > > If you post the worksheet function I could convert it to UDF code.
> > > >
> > > > The UDF gets entered into a worksheet like any other excel spreadsheret
> > > > function. functtions return one value such as True, a number, a string.
> > > >
> > > > to create a UDF do the following
> > > > 1) On worksheet menu Tools - Macro - Security
> > > > 2) check to see if the security level is low or medium. If not set to medium
> > > > 3) On worksheet menu Tools - Macro - Visual Basic Editor, normally refered
> > > > to as VBA
> > > > 4) From VBA menu Insert - Module
> > > > 5) Create a function like the one I posted
> > > >
> > > > the first line is : function function_name(parameter list)
> > > > last line is : end function
> > > > the returned value must be set someplace in the code assigning the value to
> > > > the functtion name like
> > > >
> > > > function_name = 123
> > > >
> > > >
> > > > "Maver1ck666" wrote:
> > > >
> > > > > Hi Joel
> > > > >
> > > > > Thanks for the fast reply. I don't understand what you mean though. Any
> > > > > chance you could put it simply (or step by step) for me please (has been a
> > > > > long day already!).
> > > > >
> > > > > Thanks again!
> > > > > Mav
> > > > >
> > > > > "Joel" wrote:
> > > > >
> > > > > > You are asking for a UDF macro. The UDF gets update just like any other
> > > > > > worksheet function and gets called just like every other worksheet function.
> > > > > >
> > > > > >
> > > > > > =newfunction(A1, B2:B7, "hello",7)
> > > > > >
> > > > > >
> > > > > > function newfunction(Target1 as Range, Target2 as Range, NewString as
> > > > > > string, Strikes as Integer)
> > > > > >
> > > > > > if NewString = "hello" and Strikes <= 6 then
> > > > > > newfunction = True
> > > > > > else
> > > > > > newfunction = False
> > > > > > end if
> > > > > >
> > > > > > end function
> > > > > >
> > > > > >
> > > > > > "Maver1ck666" wrote:
> > > > > >
> > > > > > > I have a number of data validation fields which depending on their value,
> > > > > > > produces a second tier of dv's.
> > > > > > >
> > > > > > > Now the problem I am having is one cell in particular has over 9 entires and
> > > > > > > Excel will only allow me to input 7 IF statements (I have looked at the
> > > > > > > Contextures site and tried using their alternatives/suggestions to no avail
> > > > > > > however the IF statements have worked with smaller entries elsewhere).
> > > > > > >
> > > > > > > So I was thinking, could I not write the IF statement in VB and get the cell
> > > > > > > (say A1) to run it after its updated (which will then use the correct dv for
> > > > > > > cell A2 depending on A1'a value). How would I get the cell to call the
> > > > > > > fucntion (if possible) please?
> > > > > > >
> > > > > > > Kind regards,
> > > > > > > Mav

When entering the =GetInvestmentName(C16) into C17, it's returning a #Name?
error within the field.

Looking back through the code, can you explain what "Select Case
InvestmentName
" does please.

"Joel" wrote:

> You just may need to force a change in the cell for it to work.
>
> First try simply to select the cell and then in the Fx box at the top of the
> worksheet click the end of the line and then hit Enter. this will force the
> functtion to re-execute.
>
> second try putting a break point in the VBA function at the 1st line by
> click the first line with the mouse and pressing F9. Go back and force and
> change to the worksheet. Then when it hits the break point press F8 to step
> through the code. It should step right through to the END FUNCTION. If it
> fails at the
> GetInvestmentName = Range("FA_Admin")
>
> then there is something wrong with the Define Name in the worksheet. I
> suspect this is the case because the INDIRECT() isn't working.
>
> check Inset - Name - Define and make sure the Referto item doesn't havve any
> quotes. It should look like the line below
> =Sheet1!$D$11
>
> "Maver1ck666" wrote:
>
> > Yes I am using named ranges with OFFSET.
> >
> > I did try using INDIRECT but it didn't work (it wouldn't produce any results
> > in the dv field). Think I read somewhere it's because of the offset formula
> > and that the 2 aren' compatable.
> >
> > Thanks for the other code but I cant seem to get Excel to call it. Am I
> > meant ot be adding the =GetInvestmentName(C16) to the dv wizard/screen, as a
> > formula???
> >
> > "Joel" wrote:
> >
> > > I don't think you need an if statement. try Indirect
> > >
> > > =indirect(C16)
> > >
> > >
> > >
> > > "Maver1ck666" wrote:
> > >
> > > > The closet I have got to any code is what I was going to use in the dv screen
> > > > (but was too long) was this:
> > > >
> > > > =IF(C16="E_Commerce",(E_Commerce),IF(C16="FA_Admin",(FA_Admin),IF(C16="Fund",(Fund),IF(C16="Investment_Admin",(Investment_Admin),IF(C16="Money_Out",(Money_Out),IF(C16="New_Business",(New_Business),IF(C16="Product",(Product),IF(C16="Servicing",(Servicing),IF(C16="Switches_Redirections",(Switches_Redirections))))))))))
> > > >
> > > > Im still confused on how C16 would run the module after it has been updated.
> > > >
> > > > Really sorry for being thick here.
> > > >
> > > > Mav
> > > >
> > > > P.S. The code above was being used for cell C17.
> > > >
> > > > "Joel" wrote:
> > > >
> > > > > If you post the worksheet function I could convert it to UDF code.
> > > > >
> > > > > The UDF gets entered into a worksheet like any other excel spreadsheret
> > > > > function. functtions return one value such as True, a number, a string.
> > > > >
> > > > > to create a UDF do the following
> > > > > 1) On worksheet menu Tools - Macro - Security
> > > > > 2) check to see if the security level is low or medium. If not set to medium
> > > > > 3) On worksheet menu Tools - Macro - Visual Basic Editor, normally refered
> > > > > to as VBA
> > > > > 4) From VBA menu Insert - Module
> > > > > 5) Create a function like the one I posted
> > > > >
> > > > > the first line is : function function_name(parameter list)
> > > > > last line is : end function
> > > > > the returned value must be set someplace in the code assigning the value to
> > > > > the functtion name like
> > > > >
> > > > > function_name = 123
> > > > >
> > > > >
> > > > > "Maver1ck666" wrote:
> > > > >
> > > > > > Hi Joel
> > > > > >
> > > > > > Thanks for the fast reply. I don't understand what you mean though. Any
> > > > > > chance you could put it simply (or step by step) for me please (has been a
> > > > > > long day already!).
> > > > > >
> > > > > > Thanks again!
> > > > > > Mav
> > > > > >
> > > > > > "Joel" wrote:
> > > > > >
> > > > > > > You are asking for a UDF macro. The UDF gets update just like any other
> > > > > > > worksheet function and gets called just like every other worksheet function.
> > > > > > >
> > > > > > >
> > > > > > > =newfunction(A1, B2:B7, "hello",7)
> > > > > > >
> > > > > > >
> > > > > > > function newfunction(Target1 as Range, Target2 as Range, NewString as
> > > > > > > string, Strikes as Integer)
> > > > > > >
> > > > > > > if NewString = "hello" and Strikes <= 6 then
> > > > > > > newfunction = True
> > > > > > > else
> > > > > > > newfunction = False
> > > > > > > end if
> > > > > > >
> > > > > > > end function
> > > > > > >
> > > > > > >
> > > > > > > "Maver1ck666" wrote:
> > > > > > >
> > > > > > > > I have a number of data validation fields which depending on their value,
> > > > > > > > produces a second tier of dv's.
> > > > > > > >
> > > > > > > > Now the problem I am having is one cell in particular has over 9 entires and
> > > > > > > > Excel will only allow me to input 7 IF statements (I have looked at the
> > > > > > > > Contextures site and tried using their alternatives/suggestions to no avail
> > > > > > > > however the IF statements have worked with smaller entries elsewhere).
> > > > > > > >
> > > > > > > > So I was thinking, could I not write the IF statement in VB and get the cell
> > > > > > > > (say A1) to run it after its updated (which will then use the correct dv for
> > > > > > > > cell A2 depending on A1'a value). How would I get the cell to call the
> > > > > > > > fucntion (if possible) please?
> > > > > > > >
> > > > > > > > Kind regards,
> > > > > > > > Mav