Excel 2016 Hyperlink Concatenate with a growing formula.

ToninoC

New Member
Joined
Jul 4, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Hi everybody.

Here using Excel 2016. I have done a worksheet to follow boats in races and friends sailing. I receive a latitude longitude point (20,00° 18,00' 32,40'' N - 60,00° 31,00' 04,80'' W) every certain amount of time (sometimes several a day, sometimes only one per day, or even less, one every 2 days. In the sheet the points are converted to decimal latitude longitude (20,3090 -60,5180). With the points the sheet calculate distance (last segment and total distance from beginning, speed and bearing). Right now the table can handle up to 5000 positions (but of course it can grow as much as needed)

Now I would like to pass this information to Windy web page (www.windy.com) to forecast the evolution. Windy format for latitude longitude is a number -+xx.xxx with decimal point, Excel decimal number is separated with comma. So I have a formula to convert decimal point to decimal comma. =SUSTITUIR(IZQUIERDA(L5;6);",";".") =SUBSTITUTE(LEFT(L5;6);",";".") (I'm using Excel spanish version).

I started adding new point by hand, with this growing formulas.

=HIPERVINCULO(CONCATENAR("Windy as forecasted";U5;",";V5;";";U6;",";V6);"Windy")
=HIPERVINCULO(CONCATENAR("Windy as forecasted";U5;",";V5;";";U6;",";V6;";";U7;",";V7);"Windy")
=HIPERVINCULO(CONCATENAR("Windy as forecasted";U5;",";V5;";";U6;",";V6;";";U7;",";V7;";";U8;",";V8);"Windy")

And so on. Soon I realized that if I receive a excel with lets say hundreds of points the work is too much. So on internet (stack overflow) the user EMM gave me this formula

ROW5 lets say AA Column = HIPERVINCULO(CONCATENAR( "Windy as forecasted"; $X5; ","; $Y5 );CONCATENAR( $X5; ","; $Y5 ) )
ROW6 - ROW5004 AA column = HIPERVINCULO(CONCATENAR( "Windy as forecasted"; AA5; ";"; $X6; ","; $Y6 ); CONCATENAR( AA5; ";"; $X6; ","; $Y6 ) )

But now I'm facing a different problem. When the formula reach 255 chars stops to work and shows #VALUE!. I have been reading posts but and I tried with VBA but without luck.

Hyperlink concatenated exceed 255 characters

21.06.SOMEWHERE LONDON MODIFICADA WINDY.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1HOJA DE CALCULO DE DISTANCIAS, VELOCIDADES Y RUMBOS NAUTICOSBARCO NOMBRE: XXXXXXXIMO: Vel. media
2MMSI: 6,79
3Eco NºFECHATIEMPO ENTRE ECOSLATITUDNORTE SURLONGITUDESTE OESTELATITUD DECIMALLONGITUD DECIMALDIST. TRAMODISTANCIA ACUM.VEL. KtVEL. Media Kt por tramoVEL. Viento kt.Rumbo vientoRUMBO
4DD/MM/AA HH:MM:SSDías HH:MMGG°MM'SS"N-SGG°MM'SS"E-WWindy LATWindy LONG
5113/03/21 14:00:0020,0018,0032,40N60,0031,0004,80W20,3090-60,518020.309-60.51820.309-60.518Wendy
6215/03/21 02:00:0001 Días 12:0022,0003,0016,50N61,0021,0024,50W22,0546-61,3568114,84114,843,1903,1915,00270,0°336,0°22.054-61.356Windy22.054-61.356Wendy
7315/03/21 14:44:0000 Días 12:4423,0001,0052,00N61,0043,0050,30W23,0311-61,730662,19177,034,8804,0413,00270,0°340,6°23.031-61.730Windy23.031-61.730Wendy
8416/03/21 02:00:0000 Días 11:1625,0012,0001,70N61,0005,0054,80W25,2005-61,0986134,78311,8111,9608,0015,00275,0°14,8°25.200-61.098Windy25.200-61.098Wendy
9516/03/21 08:00:0000 Días 06:0026,0010,0023,50N62,0049,0017,30W26,1732-62,8215110,00421,8118,3313,1715,00275,0°302,4°26.173-62.821Windy26.173-62.821Wendy
10616/03/21 14:00:0000 Días 06:0027,0003,0046,80N62,0036,0054,00W27,0630-62,615054,56476,379,0911,1315,00275,0°11,7°27.063-62.615Windy27.063-62.615Wendy
11717/03/21 02:00:0000 Días 12:0028,0052,0058,70N62,0002,0054,90W28,8830-62,0486113,32589,709,4410,2910,00275,0°15,2°28.882-62.048Windy28.882-62.048Wendy
12817/03/21 08:00:0000 Días 06:0028,0034,0059,60N62,0038,0019,10W28,5832-62,638635,90625,605,9808,14240,1°28.583-62.638Windy28.583-62.638Wendy
13918/03/21 02:00:0000 Días 18:0029,0025,0024,40N63,0033,0037,00W29,4234-63,560369,91695,503,8806,0110,00360,0°316,4°29.423-63.560Windy29.423-63.560Wendy
Principal
Cell Formulas
RangeFormula
Q2Q2=AVERAGE(P6:P5004)
L5:L13L5=IF(D5="","",IF(G5="S",(-1*(D5+E5/60+F5/3600)),(D5+E5/60+F5/3600)))
M5:M13M5=IF(H5="","",IF(K5="W",(-1*(H5+I5/60+J5/3600)),(H5+I5/60+J5/3600)))
U5:U13U5=SUBSTITUTE(LEFT(L5,6),",",".")
V5:V13V5=SUBSTITUTE(LEFT(M5,7),",",".")
N6:N13N6=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)
O6O6=IF(N6=0,"",N6)
P6:P13P6=IF(N6="","",IF(C6="","",N6/(C6*24)))
Q6Q6=IF(P6="","",AVERAGE(P6))
O7:O13O7=IF(N7="","",(N7+O6))
Q7:Q13Q7=IF(P7="","",SUM(Q6+P7)/2)
T6:T13T6=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))),2*PI()))))
W6W6=HYPERLINK(CONCATENATE("https://www.windy.com/distance/",U5,",",V5,";",U6,",",V6),"Windy")
W7W7=HYPERLINK(CONCATENATE("https://www.windy.com/distance/",U5,",",V5,";",U6,",",V6,";",U7,",",V7),"Windy")
W8W8=HYPERLINK(CONCATENATE("https://www.windy.com/distance/",U5,",",V5,";",U6,",",V6,";",U7,",",V7,";",U8,",",V8),"Windy")
W9W9=HYPERLINK(CONCATENATE("https://www.windy.com/distance/",U5,",",V5,";",U6,",",V6,";",U7,",",V7,";",U8,",",V8,";",U9,",",V9),"Windy")
W10W10=HYPERLINK(CONCATENATE("https://www.windy.com/distance/",U5,",",V5,";",U6,",",V6,";",U7,",",V7,";",U8,",",V8,";",U9,",",V9,";",U10,",",V10),"Windy")
W11W11=HYPERLINK(CONCATENATE("https://www.windy.com/distance/",U5,",",V5,";",U6,",",V6,";",U7,",",V7,";",U8,",",V8,";",U9,",",V9,";",U10,",",V10,";",U11,",",V11),"Windy")
W12W12=HYPERLINK(CONCATENATE("https://www.windy.com/distance/",U5,",",V5,";",U6,",",V6,";",U7,",",V7,";",U8,",",V8,";",U9,",",V9,";",U10,",",V10,";",U11,",",V11,";",U12,",",V12),"Windy")
W13W13=HYPERLINK(CONCATENATE("https://www.windy.com/distance/",U5,",",V5,";",U6,",",V6,";",U7,",",V7,";",U8,",",V8,";",U9,",",V9,";",U10,",",V10,";",U11,",",V11,";",U12,",",V12,";",U13,",",V13),"Windy")
C6:C13C6=IF(B6=0,"",B6-B5)
AA5AA5= HYPERLINK(CONCATENATE( "https://www.windy.com/distance/", $X5, ",", $Y5 ),CONCATENATE( $X5, ",", $Y5 ) )
AA6:AA13AA6= IF(U6=0,"",HYPERLINK(CONCATENATE( "https://www.windy.com/distance/", AA5, ";", $X6, ",", $Y6 ), CONCATENATE( AA5, ";", $X6, ",", $Y6 ) ))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
R5:S5Expression=ES.IMPAR(FILA())textNO
R5:S5Expression=ES.IMPAR(row())textNO
B5Expression=ES.IMPAR(FILA())textNO
B5Expression=ES.IMPAR(row())textNO
B6Expression=ES.IMPAR(FILA())textNO
B6Expression=ES.IMPAR(row())textNO
E7:G5004,I7:T5004,A7:C5004Expression=ES.IMPAR(FILA())textNO
E7:G5004,I7:T5004,A7:C5004Expression=ES.IMPAR(row())textNO
N5:T5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
N5:T5Cell Valuecontains " "textNO
O8:O5003,A5:A5004,C152:C5004,L205:S5004,E7:G204,D7:D5004,H7:H5004,I7:S204,B6:S6,T5:T5004,Q8:S5004,B7:C204,A5:S5Expression=ES.IMPAR(FILA())textNO
 

Attachments

  • 21.04.04.CAPTURA.JPG
    21.04.04.CAPTURA.JPG
    195.5 KB · Views: 2

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Forum statistics

Threads
1,141,001
Messages
5,703,656
Members
421,309
Latest member
ray crad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top