help hyperlink multiple cell in a cell

[Nguyen Anh Dung]

as below picture. I want hyperlink cell B2,B3,B4 ->hyperlink cell D2 and open picture when click name picture!

best regards,
Nguyen Anh Dung

 

[Momentman]

The challenge is splitting the multicell into its various values. One way may be to use a lambda

Book2
	A	B	C	D
1	c:\users\dungna\desktop\image\2\			G0050579.JPG G0050580.JPG G0050581.JPG
2		G0050579.JPG
3		G0050579.JPG
4		G0050579.JPG
Sheet2

Cell Formulas
Range	Formula
B2:B4	B2	=HYPERLINK(A1&SplitString(D1),SplitString(D1))
Dynamic array formulas.

The splitstr is a lambda

=LAMBDA(txt,TRIM(MID(SUBSTITUTE(txt,CHAR(10),REPT(" ",LEN(txt))),(SEQUENCE(LEN(txt)-LEN(SUBSTITUTE(txt,CHAR(10),""))+1)-1)*LEN(txt)+1,LEN(txt))))

or

Book2
	A	B	C	D
1	c:\users\dungna\desktop\image\2\			G0050579.JPG G0050580.JPG G0050581.JPG
2		G0050579.JPG
3		G0050579.JPG
4		G0050579.JPG
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=HYPERLINK(A1&TRIM(MID(SUBSTITUTE(D1,CHAR(10),REPT(" ",LEN(D1))),(SEQUENCE(LEN(D1)-LEN(SUBSTITUTE(D1,CHAR(10),""))+1)-1)*LEN(D1)+1,LEN(D1))),TRIM(MID(SUBSTITUTE(D1,CHAR(10),REPT(" ",LEN(D1))),(SEQUENCE(LEN(D1)-LEN(SUBSTITUTE(D1,CHAR(10),""))+1)-1)*LEN(D1)+1,LEN(D1))))
Dynamic array formulas.

 

[Fluff]

@Momentman
The OP is using 2016 therefore neither of those solutions will work.

 

[Momentman]

@Momentman
The OP is using 2016 therefore neither of those solutions will work.

Good catch!! @Fluff

We can maybe replace the SEQUENCE function with the Row/ROWS construct, but it would not be absolutely dynamic

 

[Fluff]

You're formulae don't seem to be working anyway as they all have the 1st value from D1

 

[Momentman]

You're formulae don't seem to be working anyway as they all have the 1st value from D1

Funny though that it works, just that the friendly_name shows incorrectly.

I have modified to work in Excel 2016, i hope The friendly name is why the formula appears to be long anyways

Book2
	A	B	C	D
1	c:\users\dungna\desktop\image\2\			G0050579.JPG G0050580.JPG G0050581.JPG
2		G0050579.JPG
3		G0050580.JPG
4		G0050581.JPG
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=HYPERLINK(A$1&TRIM(MID(SUBSTITUTE(D$1,CHAR(10),REPT(" ",LEN(D$1))),(ROWS($1:1)-1)*LEN(D$1)+1,LEN(D$1))),TRIM(MID(SUBSTITUTE(D$1,CHAR(10),REPT(" ",LEN(D$1))),(ROWS($1:1)-1)*LEN(D$1)+1,LEN(D$1))))

 

[Nguyen Anh Dung]

Funny though that it works, just that the friendly_name shows incorrectly.

I have modified to work in Excel 2016, i hope The friendly name is why the formula appears to be long anyways

Book2
	A	B	C	D
1	c:\users\dungna\desktop\image\2\			G0050579.JPG G0050580.JPG G0050581.JPG
2		G0050579.JPG
3		G0050580.JPG
4		G0050581.JPG
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=HYPERLINK(A$1&TRIM(MID(SUBSTITUTE(D$1,CHAR(10),REPT(" ",LEN(D$1))),(ROWS($1:1)-1)*LEN(D$1)+1,LEN(D$1))),TRIM(MID(SUBSTITUTE(D$1,CHAR(10),REPT(" ",LEN(D$1))),(ROWS($1:1)-1)*LEN(D$1)+1,LEN(D$1))))

Thanks you!!!
i using office 2016 and result as picture.

Best regards.
Nguyen Anh Dung

 

[Momentman]

Thanks you!!!
i using office 2016 and result as picture.
View attachment 36630

Best regards.
Nguyen Anh Dung

I don't see any data in D1, i thought that was where you had the multiple file names!!

 

[Nguyen Anh Dung]

I don't see any data in D1, i thought that was where you had the multiple file names!!

Thanks you!!! result i want join cell B2,B3,B4 into a cell D1 but still keep hyperlink. Not Cell D1 to B2,B3,B4

 

[Fluff]

You cannot have multiple hyperlinks in one cell.