vlookup not returning hyperlink to another cell in the workbook

[michellehemmings]

So i have a contents page which holds my vlookup

=VLOOKUP(C8,'Content Summary'!1:1048576,2,FALSE)

and a content summary page which holds a full list of sheets and sheet sections in my workbook.

The vlookup on the contents page pulls the correct data but doesn't pull through the hyperlink to the cell

I am trying to create a user friendly drop down contents page instead of people having to scroll down a 6 page document to find the relevant section in the workbook they require before clicking the link

I have tried

=HYPERLINK(VLOOKUP(C8,'Content Summary'!1:1048576,2,FALSE)​

However this doesn't work as my link is a "place in this document" link

PLEASE HELP!

 

[edmitchell]

Hi

for an address within the file, you need to add "#" before the address so your formula would become
=HYPERLINK("#" & VLOOKUP(C8,'Content Summary'!1:1048576,2,FALSE))

Regards

 

[michellehemmings]

Hi Ed

Thanks for the reply.

I have done what you mentioned and it returned an errormessage “Reference isn’t valid”

 

[edmitchell]

Hi

can you post some sample data? for example, in your data, C8 is mentioned and it would also be good to see some data from the 'Content Summary' tab (particularly what matches C8)?
I would have thought the error message means that the Vlookup is returning some data that doesn't match to an area in the main tab, but with some sample data I could 'mock it up' and check


regards

 

[michellehemmings]

Hi Ed,

Ok……The purpose of the document is a directory, I wanted a dropdown on the contents page that brought back a list of sub sections on each Consolidators “Profile Page” – Sheet. These sub sections to be clickable and take you directly to the subsection on the relevant sheet. I will have 3 of these documents in total which will hold customers, suppliers and third party details and will contain 30+ Profile pages so I don’t want people to have to scroll down 6 pages to find the section they want to go to hence the drop down list for ease.

Sheet 1 – Contents
Cell C8 has a Data Validation drop down with a list of consolidators in it – Info pulled from Content summary sheet column 1
NFT, Oakland, Yearsley ETC
Cell C9 - =(VLOOKUP(C8,'Content Summary'!1:1048576,2,FALSE))
Cell C10 - =VLOOKUP(C8,'Content Summary'!1:1048576,3, FALSE)
Cell D11 - =IF(LEN(VLOOKUP(C8,'Content Summary'!1:1048576,4, FALSE))=0,"",(VLOOKUP(C8,'Content Summary'!1:1048576,4, FALSE)))
Cell D12 - =IF(LEN(VLOOKUP(C8,'Content Summary'!1:1048576,5, FALSE))=0,"",(VLOOKUP(C8,'Content Summary'!1:1048576,5, FALSE)))
Cell D13 - =IF(LEN(VLOOKUP(C8,'Content Summary'!1:1048576,6,FALSE))=0,"",(VLOOKUP(C8,'Content Summary'!1:1048576,6,FALSE)))
Cell C 14 - ==IF(LEN(VLOOKUP(C8,'Content Summary'!1:1048576,7,FALSE))=0,"",(VLOOKUP(C8,'Content Summary'!1:1048576,7,FALSE)))

There will then be a sheet per consolidator holding a range of information (Cell references will change as data in each sub section gets added but I have put them below as an idea of current set up)
E.G Sheet 2 - NFT
Sub Sections to include
Company Information (Cell A6)
Key Contacts (Cell A18)
Accounts (Cell A20)
Customer Services (Cell A27)
Operations (Cell A34)
Addresses (Cell A41)

Sheet 3 - Oakland
Sub Sections to include
Company Information (Cell A6)
Key Contacts (Cell A18)
Accounts (Cell A20)
Customer Services (Cell A27)
Operations (Cell A34)
Addresses (Cell A41)


And repeated for each individual consolidator

Sheet 5 – Template – A blank copy of the Profile Page layout
Sheet 6 – Content Summary
A table of each sheet and sub section in that sheet with Hyperlinks to “a place in this document”
All hyperlinks work in this sheet exactly how I want them to, but are not pulling through on the contents sheet

[TABLE="width: 804"]
<tbody>[TR]
[TD]Consolidator
[/TD]
[TD]Section 1
[/TD]
[TD]Section 2
[/TD]
[TD]Section 3
[/TD]
[TD]Section 4
[/TD]
[TD]Section 5
[/TD]
[TD]Section 6
[/TD]
[/TR]
[TR]
[TD]NFT
[/TD]
[TD]Company Information
[/TD]
[TD]Key Contacts
[/TD]
[TD]Accounts
[/TD]
[TD]Customer Services
[/TD]
[TD]Operations
[/TD]
[TD]Addresses
[/TD]
[/TR]
[TR]
[TD]Oakland
[/TD]
[TD]Company Information
[/TD]
[TD]Key Contacts
[/TD]
[TD]Accounts
[/TD]
[TD]Customer Services
[/TD]
[TD]Operations
[/TD]
[TD]Addresses
[/TD]
[/TR]
[TR]
[TD]Reed Boardall
[/TD]
[TD]Company Information
[/TD]
[TD]Key Contacts
[/TD]
[TD]Accounts
[/TD]
[TD]Customer Services
[/TD]
[TD]Operations
[/TD]
[TD]Addresses
[/TD]
[/TR]
[TR]
[TD]Yearsley
[/TD]
[TD]Company Information
[/TD]
[TD]Key Contacts
[/TD]
[TD]Accounts
[/TD]
[TD]Customer Services
[/TD]
[TD]Operations
[/TD]
[TD]Addresses
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Templates
[/TD]
[TD]Template Document
[/TD]
[TD]Content Summary
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hope this all makes sense

Michelle

 

[edmitchell]

Michelle

hi - I can see a solution via VBA (which would then mean that the main directory Excel file, would be ".xlsm" rather than "xlsx"), would that work for you?


Ed

 

[michellehemmings]

Hi Ed,

I am presuming everyone can open an xlsm like an xlsx?

As long as the older generation in our company have no difficulties using it/opening I can’t see any issues.

Never worked in xlsm so wouldn’t know what the difference is in formatting/use.

Thank you so much for your help!

Michelle

 

[edmitchell]

Hi
I think there are 2 options:

Based on Excel functions (so extension remains as ‘.xlsx’)

You would need to add to the ‘Content Summary’ a column/cells which contains in text the actual hyperlink address (e.g. next to the actual ‘NFT - Company Information’ link would be a cell containing “NFT-a6”), and so on for all the other Consolidator links.[TABLE="width: 0"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Company Information[/TD]
[TD]Section 1 URL (for formulae only)[/TD]
[TD]Key contacts[/TD]
[TD]Section 2 URL (for formulae only)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]NFT[/TD]
[TD]Links to NFT Section 1[/TD]
[TD]NFT!a6[/TD]
[TD]NFT Section 2[/TD]
[TD]NFT!a18[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Oakland[/TD]
[TD]Links to Oakland Section1[/TD]
[TD]Oakland!a6[/TD]
[TD]Oakland Section 2[/TD]
[TD]Oakland!a18[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Yearsley[/TD]
[TD]Links to Yearsley Section 1[/TD]
[TD]Yearsley!a6[/TD]
[TD]Links to Yearsley Section 2[/TD]
[TD]Yearsley!a18[/TD]
[/TR]
</tbody>[/TABLE]

1. This new cell can then be referenced to make the formulae behind the hyperlinks on the ‘Contents’ page work correctly. So the formulae on Contents tab would become

=HYPERLINK("#" & VLOOKUP(C8,'Content Summary'!A:E,3,FALSE),C8 & " Section 1")

Within this formula:-

1. You could replace the ‘Section 1’ with whatever friendly text you need.
2. This Vlookup returns the new text (which is the internal reference to the cell in the file), from column C (e.g. the 3rd column) whilst continuing to look at C8 for the name of the consolidator.
3. You would need to change the ‘3’ in the vlookup to ‘5’ for use behind the Section 2/Key Contacts link, and so on for the other links, whilst expanding !A:E as needed.

This would mean you could continue with .xlsx, but would mean adding the extra columns/cells and their associated values (obviously the column(s) could then be hidden if needed), the down side of this would mean maintaining this data if anything change(s).

Based on VBA (so extension would become ‘xlsm’)

Add the following VBA code (so ALT+F11, Insert, Module)

Function GetURL_CS(Whichrow As Integer, WhichCol As Integer) As String
Dim rng As Range
    On Error GoTo 0
    Set rng = Sheets("Content Summary").Cells(Whichrow, WhichCol)
    'GetURL = rng.Hyperlinks(1).Address
    x = rng.Hyperlinks(1).SubAddress
     GetURL_CS = "#" + x
End Function

On the Contents tab, behind the Hyperlink buttons to the sections, add a formula of
=HYPERLINK(GetURL_CS(MATCH(C8,'Content Summary'!A:A,0),2),C8 & " Section 1")
You could again replace the ‘Section 1’ with whatever friendly text you need.

The ‘Content Summary’ tab would look like:
[TABLE="width: 0"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Company Information[/TD]
[TD]Key Contacts[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]NFT[/TD]
[TD]Links to NFT Section 1[/TD]
[TD]NFT Section 2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]OakLand[/TD]
[TD]Links to Oakland Section1[/TD]
[TD]Oakland Section 2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Yearsley[/TD]
[TD]Links to Yearsley Section 1[/TD]
[TD]Links to Yearsley Section 2[/TD]
[/TR]
</tbody>[/TABLE]

1. The Match in the formula above, returns the hyperlink from column 2 (e.g. ‘B’) in the row that matches the Consolidator.
2. When you need the link to return column 3 (‘C') you would just change the “!A:A,0),2” to “!A:A,0),3” (and repeat for the other columns)

I hope this makes sense



Ed

 

[michellehemmings]

Ed

This is great and exactly what I was looking for, I have used the VBA Option.

My next question is this, is it possible to have the MATCHreturn the text that’s in the content summary sheet as the headings could be differenton each worksheet rather than showing “Section 1” or “Company Information” as Ichanged it to in the below formula?

=HYPERLINK(GetURL_CS(MATCH(C8,'ContentSummary'!A:A,0),2),C8 & " Company Information")

Thank You so much for all your help!!

Michelle

 

[michellehemmings]

Hi Ed,

I have been able to solve this by adding the VLOOKUP formula to the end of your formula.

Thank you so much!!!

Michelle