goto specific cell based on that cell's text content

[MrDB4Excel]

I have a workbook that has several sheets. In sheet PorS_LFT3 I have a cell that says "See Sheet SW_LFT3 regarding translation at Stripe [C]"
In this sheet SW_LFT3 is a row that contains the text "Stripe [C]".
I am continually adding new content to the sheet SW_LFT3 which when the data sort is run then the cell that contains "Stripe [C]" changes position and no longer remains in the cell address it was.
In the sheet PorS_LFT3, I want to link at the cell that reads "See Sheet SW_LFT3 regarding translation at Stripe [C]" to the cell in the sheet SW_LFT3 that contains the text "Stripe [C]".
When I create a simple hyperlink to the cell containing "Stripe [C]" it works until I add content to the sheet SW_LFT3 and perform the data sort at which time the hyperlink no longer goes to the cell containing "Stripe [C]", but goes to the same cell location referenced in the hyperlink, but shows a different text as a result of the data sort.
Is there a way to do this without VBA so that no matter how many times data sort is run when new content is entered then when picking the cell in PorS_LFT3 it links to the cell in SW_LFT3 containing the text "Stripe [C]"?
P.S. Data sort is run when new content is added because of the need to alphabetize content. Data sort is only run in the sheet SW_LFT3.

 

[RileyC]

You can use match to find the specific instance of row where the data you want to find occurs if you don't have too much data. If you have a bunch of points to draw from I would create storage for the data to organize the points where each instance starts and ends index wise then match through that array.

 

[MrDB4Excel]

Thanks Riley, will look into how to use Match and Index

 

[Yongle]

You can use this method. The hyperlink works both before an after a data sort. Adapt to match your worksheet structures

I created a sheet named SW_LFT3 with 3 columns with the required string Stripe [C] in column C

To generate the required string for the reference, in sheet PorS_LFT3 , insert formula in B1
="SW_LFT3!"&"C" & MATCH("Stripe [C]",SW_LFT3!C:C,0)

and this formula in A1
=HYPERLINK(CELL("address",INDIRECT(B1)),"Stripe [C]")

Inexact match?
The above gets the reference string to the first EXACT match for Stripe [C] which may not suit your needs.
This formula gets the first cell containing that string
="SW_LFT3!"&"C" & MATCH("*Stripe [C]*",SW_LFT3!C:C,0)

More than one match?
But if more than one cell contain the string then adding a helper column to the data in SW_LFT3 can help
Using my example, put Stripe[C] (or preferred unique reference) into the cell in column D and amend the formula in B1 to
="SW_LFT3!"&"C" & MATCH("Stripe [C]",SW_LFT3!D:D,0)
The above returns string to link to column C, but finds the match in column D

 

[Yongle]

I forgot to add that everything can be built into one formula
I prefer using 2 cells to allow the resultant string to be verified
But, after testing, you may prefer the single cell approach

This would be the amended formula in cell A1 if not using B1 to construct the string
=HYPERLINK(CELL("address",INDIRECT("SW_LFT3!"&"C" & MATCH("Stripe [C]",SW_LFT3!C:C,0))),"Stripe [C]")

 

[MrDB4Excel]

Again many thanks, Yongle.
I am still working through the kinks because this file I am speaking of, on the sheet SW_LFT3, there are 6 columns and to date 226 rows, all containing vital information.
On the sheet PorS_LFT3, there are 8 columns and to date 1018 rows, as well containing vital information.

 

[Yongle]

Use any formula which dynamically returns the correct cell reference
- the rest is easy