=HYPERLINK function and Excel 2003 problem.

[[email protected]]

Hello,

I have a worksheet with addresses of the clients, so each row represent
one client (Name, Title;Street+no, ZIP, city ...)
I would need to put on the first column a special hyperlink, which will
be composed from the values of next columns.

Like :
=HYPERLINK("http://www.abc.abc?Parameters=Excel"&";"&C12&";"&IF(D12=$C$5;0;IF(D12=$C$6;1;IF(D12=$C$7;2;3)))&";"&E12&";"&F12&";"&G12;"Open
in browser")

So the hyperlink would look like
http://www.abc.abc?Parameters=Excel;John ****;0;Lombard street
1204;10304;Zurich ...

But the hyperlink does not work, when in one or more of the cells is a
value containing space - in this example the name and street. I've
tried to replace the spaces with %20, but still the same. Simply Excel
will not open the browser, nor display an error message in those
cases...
However, if I remove the space, it works.

This problem occurs only in Excel 2003, in XP it works..

Ajo

[[email protected]]

I think I found the solution - I had to SUBSTITUTE all spaces with +
sign...

so it should be like =HYPERLINK(SUBSTITUTE( ............ ; " " ;
"+");"Open in browser")

If somebody has found a different solution, please let me know!

Thnx!
Ajo

[email protected] wrote:
> Hello,
>
> I have a worksheet with addresses of the clients, so each row represent
> one client (Name, Title;Street+no, ZIP, city ...)
> I would need to put on the first column a special hyperlink, which will
> be composed from the values of next columns.
>
> Like :
> =HYPERLINK("http://www.abc.abc?Parameters=Excel"&";"&C12&";"&IF(D12=$C$5;0;IF(D12=$C$6;1;IF(D12=$C$7;2;3)))&";"&E12&";"&F12&";"&G12;"Open
> in browser")
>
> So the hyperlink would look like
> http://www.abc.abc?Parameters=Excel;John ****;0;Lombard street
> 1204;10304;Zurich ...
>
> But the hyperlink does not work, when in one or more of the cells is a
> value containing space - in this example the name and street. I've
> tried to replace the spaces with %20, but still the same. Simply Excel
> will not open the browser, nor display an error message in those
> cases...
> However, if I remove the space, it works.
>
> This problem occurs only in Excel 2003, in XP it works..
>
> Ajo