Dynamic hyperlink

Formulation of the problem

We have two tables on different sheets of the same book: a table with orders from customers (on the sheet Order) and a table with the client base (sheet Client). When working with the table of orders, I want to be able to quickly switch to the table with clients in order to view detailed data on the client (address, name of the director, etc.). That is, in other words, I want to have a hyperlink in each line in the table of orders, when you click on it, you will go to the sheet Client, and exactly on the line where this client is mentioned:

Something like the VLOOKUP function type, but not for the sake of data substitution, but for a quick link from one table to another.

Step 1. Create a variable with the name of the sheet

To create hyperlinks in the future, we will need to write the name of the current file and the name of the sheet each time Clientto which the links will go. Therefore, it is easier to create a variable in Excel memory with the desired value once and access it later as needed.

In Excel 2007/2010, you can use the tab formula (Formulas) and button Name Manager (Name Manager). In older versions select from the menu Insert – Name – Assign (Insert — Name — Define). In the window that opens, click the button Create (new) and enter the name of the variable there (I named it for example My_List) and the formula into a string Range (Reference):

=MID(CELL(“filename”,Customers!$A$1);SEARCH(“[“;CELL(“filename”,Customers!$A$1)); 256)&”!”

=MID(CELL(«filename»,$A$1),FIND(«[«,CELL(«filename»,$A$1)),256)&»!»

Let’s break down this structure into its components for clarity:

  1. CELL(“filename”,Customers!$A$1) – a function that, at the address of a given cell (A1 from the Clients sheet), produces any necessary data on the sheet and file. In this case, the full path to the current file up to the sheet in the form D:Working papersContracts[Budget.xls]Clients
  2. From this string, we only need the file and leaf part (without the drive and folders), so we look for the first occurrence of the open square bracket in the string using the function SEARCH (FIND) and then we cut everything from the string from this character to the end (256 characters) using the function PSTR(mid).
  3. At the end, we glue an exclamation mark to the cut out fragment with the name of the file and sheet – the standard separator for sheet names and cell addresses in formulas, because further addresses of cells will have to go.

Thus, this formula outputs the name of the current file in square brackets with an extension with the sheet name glued to it and an exclamation point. The formula is easy to check – just type in any empty cell =My_Sheet and press the key Enter.

Step 2. Create hyperlinks

Select an empty cell in the row opposite the first order and enter the following formula there:

=HYPERLINK(My_List&ADDRESS(MATCH(B2;Customers!$A$1:$A$7;0); 1);»>>»)

=HYPERLINK(My_Sheet&ADDRESS(MATCH(B2,Customers!$A$1:$A$7,0),1),”>>”)

Let’s break it down into components in the same way:

  1. Function MATCH(B2;Customers!$A$1:$A$7;0) – looks for the serial number of the cell in the range A1: A7 on the sheet Client, where the name of the current client from B2 occurs (the last argument =0 means to search for an exact match, similar to the function VPR)
  2. Function ADDRESS generates the cell address (in the form of a text string) by row and column number, i.e. the address of the cell with the desired client, where the hyperlink should then refer
  3. Then we paste a link to the file and the sheet (variable My_List) and use this as an argument to the function HYPERLINK (HYPERLINK), which, in fact, creates the link we need.

If desired, you can replace the external representation of the hyperlink from the banal symbols “>>” to something more interesting using the function SYMBOL (CHAR), which can display non-standard characters by their codes:

=ГИПЕРССЫЛКА(Мой_Лист&АДРЕС(ПОИСКПОЗ(B2;Клиенты!$A$1:$A$7;0);1);SYMBOL(117))

So, for example, if you use the Wingdings 3 font and the symbol with code 117, you can get these nice hyperlink icons:

  • Using the VLOOKUP function to substitute values
  • Creating emails with the HYPERLINK function

 

Leave a Reply