I have a Excel sheet that calculates distance, speed and bearing of a boat given a waypoint in degree, minutes and seconds (I use it to follow races and friends). The sheet supports up to 5000 points. I want to add a column with a hyperlink to www.windy.com to plot the points in windy website.
I already have a concatenated formula that create the hyperlink but I add every value by myself.
1st. =HIPERVINCULO(CONCATENAR("https://www.windy.com/distance/";U5;",";V5;";";U6;",";V6);"Windy")
2nd. =HIPERVINCULO(CONCATENAR("https://www.windy.com/distance/";U5;",";V5;";";U6;",";V6;";";U7;",";V7);"Windy")
3rd. =HIPERVINCULO(CONCATENAR("https://www.windy.com/distance/";U5;",";V5;";";U6;",";V6;";";U7;",";V7;";";U8;",";V8);"Windy")
4rd. =HIPERVINCULO(CONCATENAR("https://www.windy.com/distance/";U5;",";V5;";";U6;",";V6;";";U7;",";V7;";";U8;",";V8;";";U9;",";V9);"Windy") and so on
The link uses values in columns V and W because windy needs the values with decimal point instead decimal comma.
Here is the table
The problem that i'm facing is how to automaticaly/recursive add the points that are inserted. Now I add to the Concatenate/hyperlink formula the new point. But I would like to have a routine/algorithm to automatically do the concatenate in every row until the last one (row 5004).
Thanks a lot.
PS. If someone thinks that would like to have the excel sheet or find it usefull I can send a copy.
05.07.21 10:13
Hi all, thanks Ron Rosenfeld and EEM for your answers. EEM the formula you gave me worked perfectly. Thanks a looot. I only had to do minor change. But now I'm facing a different problem. Sorry.
Problem now is the 255 chars hyperlink limit. After 15 iterations the formula doesn't work anymore and I only get #¡VALOR! #VALUE!.
I have been trying before asking again, but without luck. Seems to be a way to solve this "behaviour" with VBA but I can't figure out.
As Mr. Rosenfeld required more information, here are the formulas used.
As you can see in column W there is a link with the growing formula I started with. The formula provided by EEM is in column AA.
DECIMAL LATITUDE ROWS L5:L5004 =IF(D5="","",IF(G5="S",(-1*(D5+E5/60+F5/3600)),(D5+E5/60+F5/3600)))
DECIMAL LONGITUDE ROWS M5:M5004 =IF(H5="","",IF(K5="W",(-1*(H5+I5/60+J5/3600)),(H5+I5/60+J5/3600)))
www.windy.com uses . as decimal separator instead ,
CHANGE Excel , to windy .
ROWS U5:U5004 =SUBSTITUTE(LEFT(L5,6),",",".")
ROWS V5:V5004 =SUBSTITUTE(LEFT(M5,7),",",".")
Formulas to create the hyperlink (A million thanks to EEM)
ROW AA5 = HYPERLINK(CONCATENATE( "https://www.windy.com/distance/", $X5, ",", $Y5 ),CONCATENATE( $X5, ",", $Y5 ) )
ROW AA6:AA5004 = IF(U6=0,"",HYPERLINK(CONCATENATE( "https://www.windy.com/distance/", AA5, ";", $X6, ",", $Y6 ), CONCATENATE( AA5, ";", $X6, ",", $Y6 ) ))
As said, after 14 iterations the hyperlink grows more than 255 characters.
www.windy.com format to create a route latitude, longitude;
Formulas used to calculate distance and bearing (just in case someone needs them)
DISTANCE =IF(L6="","",(6371*ACOS(COS(RADIANS(90-L5))*COS(RADIANS(90-L6))+SIN(RADIANS(90-L5))*SIN(RADIANS(90-L6))*COS(RADIANS(M5-M6))))*0.539956)
BEARING =IF(P6=0,"",IF(L6="","",DEGREES(MOD(ATAN2((COS(RADIANS(L5))*SIN(RADIANS(L6)))-(SIN(RADIANS(L5))*COS(RADIANS(L6))*COS(RADIANS(M6-M5))), SIN(RADIANS(M6-M5))COS(RADIANS(L6))),2PI()))))
Thanks everybody for your time and knowledge.
13.07.21. NO LUCK. I have been incapable of doing it. But I opened the file in OPEN OFFICE and with a little bit of formatting it's working flawlessly.
It's annoying the 255 chars limit in Excel, but I'm not going to break my head any more.
Thanks a lot EEm.
