HYPERLINK - go to a reference in a named range on a different sheet in the same workbook

[ScruffyW1lf]

Hi, can anyone help? I have a columnar SUMMARY listing in numerical order 1,2,3, etc of "schemes" that represent extents in a SCHEMATIC on a different sheet.
The "schemes" (1,2,3,4, etc) lie in the SCHEMATIC sheet within a named range called "SCHEMA". What the SUMMARY listing does is pull together some calculated summary information from the SCHEMATIC into one row per scheme. The named range SCHEMA has multiple "1"s, "2"s etc - the precise count of each number depending on where a particular scheme starts and ends in terms of cell count i.e. they are variable and changeable.

I need to create a hyperlink from the SUMMARY listing back to the SCHEMATIC so that other users use the SUMMARY to go straight to any scheme in the SCHEMATIC of their choice/interest.

So, the key references for the hyperlink are expected (by me) to be

1. Scheme number in the SUMMARY listing (e.g. Cell B18)
2. The named range SCHEMA where the scheme number to hyperlink exists
3. The first instance of the scheme number within the range

Is this simple/complicated? looked at various guides but few deal with named ranges, so still clueless.

 

[OaklandJim]

This sounds doable. But I'll need some data to work with.

If the data is not confidential post a link to your workbook. If necessary you can enter fake-but-realistic data before providing the link. Put the file(s) on Dropbox, Box, 1Drive, Google Drive etc. Use the link icon above the Mr Excel message area. Make sure that other people can access the file!

Or, consider sharing relevant data using Mr Excel's excellent XL2BB addin that enables you to post a portion of a worksheet. See XL2BB - Excel Range to BBCode for details.

 

[Ludwig]

Not sure if you're still monitoring this post ScruffyW1lf but from what I read of your query, the formula below should work.

=IF( C18 = "", "",
IFERROR( HYPERLINK( "#Schematic!" & ADDRESS( MATCH( C18, SCHEMA, 0 ) + 2, 2, 1 ), "Schematic " & C18 ), HYPERLINK( "#B" & ROW( B18 ), "entry not found" ) )
)

• The reason for using "row" in the hyperlink formula when no match found is to provide a dynamic way to reference itself so Excel will stay on the selected/clicked cell rather than trying to go to somewhere in the other worksheet that doesn't match. You will just need to 'filldown' the formula for many rows.
• The "+ 2" after MATCH is to allow for the number of rows in the other sheet before the first cell in your named range SCHEMA.
• You could replace "+ 2" by a more dynamic value (INDEX( ROW( SCHEMA ), 1 ) - 1) - you subtract one to get the rows *before* the start row.
  You need to use index otherwise it will return an array containing all row numbers in the named range.

Using table references rather named range would be even nicer, but I assume you haven't used these.