Hyperlink friendly name assigned to result using random list

[grinfactor]

Hi all,
I hope that someone can help me with this..

1. I have long list of URL addresses that I want to have displayed on a random basis. I only have space for 5 at a time
2. In column A of sheet 1, I manually input the URL addresses on a regular basis (these are news items so needs to be kept up to date)
3. In Column B I have entered =RAND() for all cells that have a corresponding URL address
4. On Sheet 2 (main display sheet) I want to display the random list of 5 URL's. I have used :
=HYPERLINK(INDEX('Data Breach'!$A$1:$A$6,RANK('Data Breach'!B1,'Data Breach'!B1:B6)))

5. This works fine, but it displays the URL address which is to long for the cell in Sheet 2
6. I want to use a 'friendly name' for each of the the URL's that has a hyperlink to the URL, so the user can double click the cell in sheet 2 when displayed and get take to the relevant address
7. I know I can do this with a normal hyperlink as I already have a link for specific reports that has a friendly name assigned, but these reports are fixed and not random:
=IFERROR(HYPERLINK(INDIRECT("'collateral'!C"&SMALL(Collateral!A:A,1)),"Report 1"),"")

7. I've tried to embed the url into a friendly name (on sheet 1 column A) but when it shows on sheet 2 it says it 'cannot open specified file'. Had this problem before

8. Can some let me know if what I am after is possible in Excel, dont want to use VBA/Macro's if possible as I have no experience of using these

Thanks

Grinfactor

 

[Andrew Poulsom]

Welcome to MrExcel.

The HYPERLINK function has friendly name as its second argument, eg:

=HYPERLINK(INDEX('Data Breach'!$A$1:$A$6,RANK('Data Breach'!B1,'Data Breach'!B1:B6)),"Friendly name here")

 

[grinfactor]

Welcome to MrExcel.

The HYPERLINK function has friendly name as its second argument, eg:

=HYPERLINK(INDEX('Data Breach'!$A$1:$A$6,RANK('Data Breach'!B1,'Data Breach'!B1:B6)),"Friendly name here")

Hi Andrew,
Thanks for the fast response, the problem I have is that the friendly name will need to change based on the URL topic that is picked up:

Here is an excerpt from my Data Breach table:

URL Address	Random	Friendly Name
20 Million People Fall Victim to South Korea Data Leak | SecurityWeek.Com	0.279686409	Korean Data Leak
http://www.securityweek.com/attacker-steals-data-2-million-vodafone-germany-customers	0.954594841	Vodaphone Germany
http://dealbook.nytimes.com/2014/10/02/jpmorgan-discovers-further-cyber-security-issues/?_php=true&_type=blogs&_r=1	0.01926842	JP Morgan Cyber Attack
http://www.theguardian.com/world/2014/aug/28/federal-police-mistakenly-publish-metadata-from-criminal-investigations	0.232506526	Australian Federal Police
http://techcrunch.com/2014/08/18/chinese-hackers-steal-personal-data-from-4-5m-community-health-systems-patients/	0.674855641	Chinese Attack Health System
Stolen personal data of 86,000 north Londoners put up for sale on dark web - Crime & Court - Hampstead Highgate Express	0.688109413	Londoners Data Up for Sale

<colgroup><col><col><col></colgroup><tbody>
</tbody>

The output from =HYPERLINK(INDEX('Data Breach'!$A$1:$A$6,RANK('Data Breach'!B1,'Data Breach'!B1:B6)) then goes into the following cells on Sheet1. But I need the friendly name with the hyperlink embedded as the hyperlink is too long

Examples of recent industry breaches that would have been mitigated

Federal police mistakenly publish metadata from criminal investigations | Australia news | The Guardian

20 Million People Fall Victim to South Korea Data Leak | SecurityWeek.Com

Stolen personal data of 86,000 north Londoners put up for sale on dark web - Crime & Court - Hampstead Highgate Express

http://dealbook.nytimes.com/2014/10...r-security-issues/?_php=true&_type=blogs&_r=1

Insider Steals Data of 2 Million Vodafone Germany Customers | SecurityWeek.Com

<colgroup><col></colgroup><tbody>
</tbody>

Thanks

 

[Andrew Poulsom]

Can't you use?

=HYPERLINK(INDEX('Data Breach'!$A$1:$A$6,RANK('Data Breach'!B1,'Data Breach'!B1:B6)),INDEX('Data Breach'!$C$1:$C$6,RANK('Data Breach'!B1,'Data Breach'!B1:B6)))

 

[grinfactor]

Can't you use?

=HYPERLINK(INDEX('Data Breach'!$A$1:$A$6,RANK('Data Breach'!B1,'Data Breach'!B1:B6)),INDEX('Data Breach'!$C$1:$C$6,RANK('Data Breach'!B1,'Data Breach'!B1:B6)))

Thanks Andrew, exactly what I was after...appreciate the fast response and help