seeking hyperlink formula

[zami]

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? TIA

 

[Scott Huish]

Change the name of your workbook in this, but perhaps:

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

 

[zami]

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]

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]

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]

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]

=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

 

[Scott Huish]

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]

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!

 

[zami]

oops, never mind