Hyperlink Formula

[Mplz]

Hello All,

I was sent a file that I am trying to analyze to be able to modify going forward. This was made at another site by an employee that no longer works there so I have no real reference on how it is supposed to work. The main issue is with the Hyperlink function and potentially using files outside of the workbook. This workbook is a time calculator of sorts, it has a main sheet with all of the employees at the site and hyperlinks to each employee. The hyperlink opens a sheet with that employee's information as well as their sick time etc.

The issue I'm running into is the reference inside of the hyperlink formula.

=HYPERLINK("#employee.1",+'Doe, John'!$L$4)

There is no sheet named "Employee 1", and all of the hyperlinks are formatted this way with the 'link_location' being #employee.1 and their 'friendly name' as their name with the cell reference.

I've poked around and tried to hyperlink a name with the excel feature instead of the formula and inside of the "Place in this Document" is a list of Defined Names being 'Lastname, Firstname'!employee.1 for every employee with their actual names in place of lastname/firstname. There is also defined names for all employees with the ending reference being "!enter_absent", "!enter_personal" etc. that I assume are being used somewhere in the workbook.

I think my main issue is I'm not familiar with defined names and how they work with referencing inside of the workbook. I am googling away but wanted to see if anyone had some suggestions on how I can start to comprehend the functionality and how the hyperlinks are opening up that employees sheet when the cell is clicked. There is VBA to print, close the employees tab to go back to the main dashboard, but each of the employees sheets have no code so this looks like it's all built in functionality. Any help is greatly appreciated!

 

[zero269]

The issue I'm running into is the reference inside of the hyperlink formula.

=HYPERLINK("#employee.1",+'Doe, John'!$L$4)

There is no sheet named "Employee 1", and all of the hyperlinks are formatted this way with the 'link_location' being #employee.1 and their 'friendly name' as their name with the cell reference.

Hi Mplz,
According to Ablebits, that # sign is used to reference the active workbook.

3 ways to insert a Hyperlink to another Excel sheet

Learn 3 ways to insert a hyperlink in Excel; add a hyperlink to another worksheet, workbook, etc; change its destination and format.

www.ablebits.com

So, it appears that hyperlink is supposed to refer to a Sheet called 'Doe, John' in that workbook. That employee.1 is supposed to be part of that Sheet name, but I can't say for sure. The L4 is the cell that would be selected when using the link. That + sign is a mystery. I'm not finding any usecases online.

Microsoft's article doesn't even mention that # sign...

HYPERLINK function - Microsoft Support

Syntax: HYPERLINK(link_location, [friendly_name])

support.microsoft.com

You may need to rebuild the hyperlink - assuming you know where the destination is. That # sign makes me believe it's in that workbook.

I played with a few examples and these are what I cam up with:
Note: My Sheetname has a space, so I had to use single quotes for the name, and that is within the double quotes. Cell G4.

VBA Testing.xlsm
	F	G
1		Doe, Jane
2	Filepath	D:\Employees\Doe, Jane.xlsx
3	Filepath + Friendly Name	Doe, Jane
4	Sheet in This Workbook	Doe, Jane
5	Build Filepath using "&"	Doe, Jane
Hyperlinks

Cell Formulas
Range	Formula
G2	G2	=HYPERLINK("D:\Employees\Doe, Jane.xlsx")
G3	G3	=HYPERLINK("D:\Employees\Doe, Jane.xlsx","Doe, Jane")
G4	G4	=HYPERLINK("#'Doe, Jane'!A1","Doe, Jane")
G5	G5	=HYPERLINK("D:\Employees\" & $G$1 & ".xlsx","Doe, Jane")

 

[Mplz]

Hi Mplz,
According to Ablebits, that # sign is used to reference the active workbook.

3 ways to insert a Hyperlink to another Excel sheet

Learn 3 ways to insert a hyperlink in Excel; add a hyperlink to another worksheet, workbook, etc; change its destination and format.

www.ablebits.com

So, it appears that hyperlink is supposed to refer to a Sheet called 'Doe, John' in that workbook. That employee.1 is supposed to be part of that Sheet name, but I can't say for sure. The L4 is the cell that would be selected when using the link. That + sign is a mystery. I'm not finding any usecases online.

Microsoft's article doesn't even mention that # sign...

HYPERLINK function - Microsoft Support

Syntax: HYPERLINK(link_location, [friendly_name])

support.microsoft.com

You may need to rebuild the hyperlink - assuming you know where the destination is. That # sign makes me believe it's in that workbook.

I played with a few examples and these are what I cam up with:
Note: My Sheetname has a space, so I had to use single quotes for the name, and that is within the double quotes. Cell G4.

VBA Testing.xlsm
	F	G
1		Doe, Jane
2	Filepath	D:\Employees\Doe, Jane.xlsx
3	Filepath + Friendly Name	Doe, Jane
4	Sheet in This Workbook	Doe, Jane
5	Build Filepath using "&"	Doe, Jane
Hyperlinks

Cell Formulas
Range	Formula
G2	G2	=HYPERLINK("D:\Employees\Doe, Jane.xlsx")
G3	G3	=HYPERLINK("D:\Employees\Doe, Jane.xlsx","Doe, Jane")
G4	G4	=HYPERLINK("#'Doe, Jane'!A1","Doe, Jane")
G5	G5	=HYPERLINK("D:\Employees\" & $G$1 & ".xlsx","Doe, Jane")

Amazing thank you for the links they were definitely helpful. I've yet to fully figure it out, but the "Employee.1" is a defined name that is then referencing their individual sheets named after each employee. Seems like a weird way to reference but I didn't make it so I'll keep digging. Thanks again