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="Investme nt_Admin",(Investment_Admin),IF(C16="Money_Out",(M oney_Out),IF(C16="New_Business",(New_Business),IF( C16="Product",(Product),IF(C16="Servicing",(Servic ing),IF(C16="Switches_Redirections",(Switches_Redi rections))))))))))

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="Investme nt_Admin",(Investment_Admin),IF(C16="Money_Out",(M oney_Out),IF(C16="New_Business",(New_Business),IF( C16="Product",(Product),IF(C16="Servicing",(Servic ing),IF(C16="Switches_Redirections",(Switches_Redi rections))))))))))

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="Investme nt_Admin",(Investment_Admin),IF(C16="Money_Out",(M oney_Out),IF(C16="New_Business",(New_Business),IF( C16="Product",(Product),IF(C16="Servicing",(Servic ing),IF(C16="Switches_Redirections",(Switches_Redi rections))))))))))

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