Automatically change hyperlink

[superfb]

Hi,

I have a hyperlink I use and there is a cell I manually enter to put a client's names. As a result the hyperlink concatenates this to form the hyperlink.

However, the directory is going to be split from A-L and M - Z.

Is there a formula that can pick up the first letter of the client name? Then determine by a if what file directory should be used....

 

[offthelip]

=IF(CODE(LEFT(A1,1))>76,"put then m to z code in here","else put the a to l code in here")

Assuming the name is in A1

 

[superfb]

Thank you for this. But some folders have (b) or ($) is there a way to exclude this and find the first letter.....

 

[superfb]

that works a treat, however,

i have an if formula that isnt working,

=if(f5= "",

IF(CODE(LEFT(E1,1))<=75,(HYPERLINK(H1&":"&"\HomeStart\Information"&""&"A-K"&$C$5,H1&":"&""\HomeStart\Information"&""&"A-K"&$C$5)),

IF(CODE(LEFT(E1,1))>75,(HYPERLINK(H1&":"&""\HomeStart\Information"&""&"L-Z"&$C$5,H1&":"&""\HomeStart\Information"&""&"L-Z"&$C$5)

if F5 isnt blank then use,

IF(CODE(LEFT(E1,1))<=75,(HYPERLINK(H1&":"&"\HomeStart\Information"&""&"A-K"&$C$5,H1&":"&""\HomeStart\Information"&""&"A-K"&$C$5&$d$5)),

IF(CODE(LEFT(E1,1))>75,(HYPERLINK(H1&":"&""\HomeStart\Information"&""&"L-Z"&$C$5,H1&":"&""\HomeStart\Information"&""&"L-Z"&$C$5&$d$5)

 

[offthelip]

you have got a number of redundant ampersands which is probably causing you an error:
Try something like this:

=IF(CODE(LEFT(E1,1))<=75,(HYPERLINK(H1&":\HomeStart\InformationA-K"&$C$5,H1&":\HomeStart\InformationA-K"&$C$5&$D$5)))

What I usually do in these circumstancers is take the "=" sign out of the equation so the VBA just write text into the cell, then add the equals sign in manually and excel will show you where the error is.