Excel 2016 Hyperlink Concatenate with a growing formula.

T

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
www.mrexcel.com

How do I do a hyperlink that exceeds 255 characters?

Say I want to create a proxy hyperlink like this: =HYPERLINK("http://www.google.com/search?q="&B3&"&safe=active","Search Google") but the URL address I'm going to use is way too big for the cell (I think max is 255 characters). How do I do that? :eeek: Thanls!
www.mrexcel.com www.mrexcel.com
www.mrexcel.com

Concatenating hyperlinks longer than 255 characters?

Hey all, I'm trying to automate the creation of a bunch of hyperlinks that I will use regularly. This is in relation to JIRA which, you might know, can have very long hyperlinks since all arguments of a search will appear in the link itself. This is important for me since changing a few cells...
www.mrexcel.com www.mrexcel.com

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: 16

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
You must log in or register to reply here.

Similar threads

J
  • Question Question
out of my league, need help
Replies
1
Views
94
Simadri1986
S
P
  • Solved
Calculating shortest to longest distances between Zip Codes (using latitude and longitude)
2 3
Replies
25
Views
2K
pawcoyote
P
V
  • Solved
Average Function
Replies
8
Views
381
Peter_SSs
Peter_SSs
L
  • Question Question
Send cell values to 2 other sheets according to condition
Replies
2
Views
199
leobrice
L
gtd526
  • Solved
Ignore cell IF error
Replies
2
Views
949
gtd526
gtd526

Forum statistics

Threads
1,223,885
Messages
6,175,183
Members
452,615
Latest member
bogeys2birdies

Share this page

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
Back
Top