Reference Dynamic Table Header Name in Hyperlink

[trishcollins]

Okay, I have several tables on several worksheets. I have another table that lists all the formulas from the other tables, so that if I ever get hit by a bus, and someone wipes out the original formula in the other table, they can find it here. I am only using formulas in this spreadsheet, as VBA, although ideal, is too hard to maintain. This is temporary tool. Anyway, I have about 40 rows in this table, and rather than individually create a hyperlink, which is very time consuming but doable, I want to use the table name and the header name I have in my table already, to be used in the Hyperlink formula. I have been trying to figure this out for two days, using various combinations of INDEX, MATCH, ADDRESS, CELL, INDIRECT, ROW, etc. to no avail. Here is a sample of the table with the data. I need to reference this with the Hyperlink formula to the actual header name in the table. Any suggestions?

Table Name	Sheet Name	Header Name	Hyperlink
Client_Use_Cases	Use Case Connectivity Patterns	Target Application Access Zone
Client_Use_Cases	Use Case Connectivity Patterns	Conditions
Client_Use_Cases	Use Case Connectivity Patterns	Source Network
Business_Needs_Matrix	Business Needs Matrix	Mapped to Preferred Network Connectivity
Preferred_Network_Connectivity_Mapped_to_Network_Paths	Net Conn Mapped to Network Path	CSP Target Type

 

[Skybluekid]

This may be the answer to your query

 

[trishcollins]

This is a bit tricker. I am trying to reference using the HYPERLINK formula. The absolute name references work, but if I want to dynamically reference them, it doesn't work:

This works: =HYPERLINK(CELL("address",Client_Use_Cases),Client_Use_Cases[[#Headers],[Target Application Access Zone]])

This doesn't work; =HYPERLINK(CELL("address",INDIRECT([@Table Name]),INDIRECT([@Table Name])[[#Headers],INDIRECT[@Target Application Access Zone]])

I am sure it's just how I have put in the INDIRECT references, and I am pretty sure someone has done this at some point.

 

[Skybluekid]

Try putting "" around the references in the INDIRECT

 

[trishcollins]

Try putting "" around the references in the INDIRECT

I did the following and resolved the issue, but it took two columns. In the first column I concatenated the necessary table and column names with the rest of the contents of what the Hyperlink needed:

I created a new column called "Hyperlink Helper" and entered this formula:
=CONCATENATE([@[Table or Range Names]]&",",[@[Table or Range Names]]&"[[#Headers],",[@[Header or Range Name]])
Which gave me this result:
Client_Use_Cases,Client_Use_Cases[[#Headers],Target Application Access Zone

I then put this in the Hyperlink cell:
=HYPERLINK(CELL("address",[@[Hyperlink Helper]]),[@[Header or Range Name]])

AND IT WORKED!