seeking hyperlink formula

zami

New Member
Joined
Jul 23, 2005
Messages
45
after trying in vain to customize one of the many hyperlink formula examples available out here, I'm stumped. Most examples show linking between 2 sheets; I'm going for the 3D affect.

Example: Index of sheet names begin on Sheet1!A4. Sheet2!B2 needs a hyperlink referencing the destination sheet name in Sheet1!A4 to jump to the destination cell in Sheet3!A4, and so on.

surely this is doable? :confused: TIA
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Change the name of your workbook in this, but perhaps:

=HYPERLINK("[Book1]"&Sheet1!A4&"!"&Sheet3!A4)
 

zami

New Member
Joined
Jul 23, 2005
Messages
45
thanks, but I was looking more towards a relative formula catching the active workbook filename rather than an absolute workbook name, as this will be applicable to many workbooks, with new workbooks each month. The hyperlinks would be internal to the active workbook, however.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Perhaps:

=HYPERLINK("[" &MID(CELL("filename",A1),(FIND("[",CELL("filename",A1))+1),(FIND(".xls",CELL("filename",A1)))-(FIND("[",CELL("filename",A1))+1)) &"]"&Sheet1!A4&"!"&Sheet3!A4)
 

zami

New Member
Joined
Jul 23, 2005
Messages
45

ADVERTISEMENT

I get a "cannot open the specified file" error. It appears the link is referencing the value of the destination cell instead of the cell address (A4) on Sheet3.

Sheet1 (auto-updated Index) and Sheet2 (Summary) are absolute sheetnames; for various reasons, they need to be separate. All other added sheets have variable/relative sheetnames, thus the formula cannot be absolute regarding destination sheetnames for the hyperlinks. I know INDIRECT is used for referencing relative sheetnames and such, and although I've had some success with this, I'm just not familiar enough yet in tying all the necessary formula functions together in correct syntax to achieve the desired result.

thanks for taking the time to share your knowledge. it is greatly appreciated.

z
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Maybe I'm missing something, I thought you wanted to create a hyperlink to take you to the Sheet name (which is in cell A4 on Sheet1) and to the cell on this sheet (listed in cell A4 on Sheet3) in the current workbook. Is this not correct?
 

zami

New Member
Joined
Jul 23, 2005
Messages
45

ADVERTISEMENT

=HYPERLINK("[" &MID(CELL("filename",A1),(FIND("[",CELL("filename",A1))+1),(FIND(".xls",CELL("filename",A1)))-(FIND("[",CELL("filename",A1))+1)) &"]"&Sheet1!A4&"!"&Sheet3!A4)

You are correct. That is what I need. Perhaps I'm misreading the formula. If one considers the Sheet1 reference as a constant, and Sheet3 (or Sheet4 or Sheet5, etc.) in the formula as the changing/relative destination cell, wouldn't I need to manually adjust my formula for each new sheet it may be referring to? (edited to add: see further explanation in my response below)

The results of the above formula show the embedded destination link as [Book1]Sheet3!Test where "Test" is the cell value, instead of [Book1]Sheet3!A4 with A4 being the destination cell reference. Clicking on the hyperlink results in "cannot open specified file" error.

and I always forget to name my game (version): using Excel 2000 on XP :eek:
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
OK, sorry this is getting confusing:

Can you please give an example of what is on Sheet1, cell A4 and what is on Sheet 3, cell A4 and what would you want it to link to exactly?
 

zami

New Member
Joined
Jul 23, 2005
Messages
45
I'll do my best; sorry it's so complicated.


Sheet1(Index)_______Sheet2(Summary)_________All other Sheets (Ex: Nov 13)
____Col A__________Col A______Col B___________Col A_____Col B________Col C
4__Nov 2______4__08680____98.76%_____4__Smith___Descriptive___Action
5__Nov 8______5__08742____96.59%_____5__Davis____Descriptive____Action
6__Nov 13_____6__09017____99.32%_____6__Jones____Descriptive____Action
7__Nov 15_____7__07995___100.00%_____7__Adams___Descriptive_____--__
_______________8__ 08401____91.87%_____8__Smith____Descriptive____Action
_______________9__09111____97.20%_____9__Davis____Descriptive____Action

Sheet1 auto-indexes sheetnames of all sheets added to the workbook. Sheet2 is a Summary of selected data in about a dozen columns from all sheets and basically fills out itself via preset cell formulas as new sheets are added. I'd like cells in Col A of the Summary sheet to hyperlink to the respective Col B Descriptives on whatever date sheets by referencing the sheetname from from Index!A:A. Each new sheet has a variable number of rows of data added on any given day (sheet names reflect date new sheets are added). My goal is to have the whole kaboodle self-sustaining; i.e., I want to set it and forget it. a template basically with no muss or fuss with adjusting formula sheetnames for a slew of new workbooks every month, etc. I know, call me crazy.

Assumption scenario of above:
Summary!A4:A5 rows are populated when a new sheet is added on Nov 2.
Summary!A6:A7 rows populate from new sheet on Nov 8.
Summary!A8 row populates from the Nov 13 sheet.
Summary!A9:A12 from the Nov 15 sheet, etc.

Let's say the viewer of the Summary sheet wants further details on the low percentage (in red) in Summary!B8. He/she would hyperlink (blue) from the ID# in Summary!A8, reference it's sheetname from Index!A6, and hop to it's corresponding Descriptive on Nov13!B4.

It actually works very well otherwise, given it's performing entirely off cell formulas with unknown variables (as to when new sheets are added, to how many lines, etc.). Now if only the dang hyperlinks would perform properly; therein my dilemma.

comments/suggestions are most welcome. thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top