Dynamic Hyperlink

[MrDB4Excel]

In the Xl2bb shown below, you see in cell A3 the value A21 which is based on cell C21.
The issue I have is when a listed program changes row number in sheet ProgramLocInfo then I need cell A3’s value to reflect that in the hyperlink.
When a program changes row number in sheet ProgramLocInfo then obviously based upon the formulas in the relevant cells between B21 to D33 the values in the range A3:A15 change as well, but not the hyperlinks.
Is there a formula that will do this?
As you can see in the Xl2bb insert I have tried some formulas that you see in B17, B18, & B19. These did not work. I also tried a multitude of other suggestions found via my web search for an answer and I was unable to get anywhere.
p.s. I did not try one I just found on MrExcel: =IF(ISREF(INDIRECT("'"&A1&"'!A1")),HYPERLINK("#"&A1&"!A1",A1),"") because not sure how I would implement it for my specific need.
p.p.s. I also was not sure about this one: Dynamic excel hyperlink
Also, the Excel sheet I am working on is already an xlsm file so I wouldn’t be opposed to VBA, but would much rather stick with formulas.
Any help will be much appreciated.

ProgramListRebuild.xlsm
	A	B	C	D	E
1		Rebuild/ Reinstall: Y = Yes, N = No, M = Maybe
2		File Mgmt. Programs		1
3	A21	calibre 64bit - E-book management	1	Y
4	A24	CloneSpy 3.42 - 64 bit	2	Y
5	A29	DExplorer	3	Y
6	A30	Directory List & Print (Pro)	4	Y
7	A32	DoubleFinder	5	Y
8	A33	DSynchronize	6	Y
9	A35	Duplicate Filter	7	Y
10	A38	FileASSASSIN	8	Y
11	A39	Files Inspector	9	Y
12	A57	Karen's Directory Printer	10	M
13	A90	ReNamer	11	Y
14	A109	Stellar Repair for Excel & Word	12	Y
15	A123	Wise Duplicate Finder	13	Y
16
17		=HYPERLINK("#"&CELL("address",INDEX(Programs_1-'3'!C21:C33,MATCH(C21,Programs_1-'3'!C21:C33,0))),C21)
18		=HYPERLINK("#"&"'ProgramLocInfo'!a21",A21)
19		=HYPERLINK("#ProgramLocInfo!A"&c21)
20
21	$A$21	[ProgramListRebuild.xlsm]ProgramLocInfo!$A$21	A21	$A$21	1
22	$A$24	[ProgramListRebuild.xlsm]ProgramLocInfo!$A$24	A24	$A$24	2
23	$A$29	[ProgramListRebuild.xlsm]ProgramLocInfo!$A$29	A29	$A$29	3
24	$A$30	[ProgramListRebuild.xlsm]ProgramLocInfo!$A$30	A30	$A$30	4
25	$A$32	[ProgramListRebuild.xlsm]ProgramLocInfo!$A$32	A32	$A$32	5
26	$A$33	[ProgramListRebuild.xlsm]ProgramLocInfo!$A$33	A33	$A$33	6
27	$A$35	[ProgramListRebuild.xlsm]ProgramLocInfo!$A$35	A35	$A$35	7
28	$A$38	[ProgramListRebuild.xlsm]ProgramLocInfo!$A$38	A38	$A$38	8
29	$A$39	[ProgramListRebuild.xlsm]ProgramLocInfo!$A$39	A39	$A$39	9
30	$A$57	[ProgramListRebuild.xlsm]ProgramLocInfo!$A$57	A57	$A$57	10
31	$A$90	[ProgramListRebuild.xlsm]ProgramLocInfo!$A$90	A90	$A$90	11
32	$A$109	[ProgramListRebuild.xlsm]ProgramLocInfo!$A$109	A109	$A$109	12
33	$A$123	[ProgramListRebuild.xlsm]ProgramLocInfo!$A$123	A123	$A$123	13
Programs_1-3

Cell Formulas
Range	Formula
D3	D3	=ProgramLocInfo!G21
D4	D4	=ProgramLocInfo!G24
D5:D6	D5	=ProgramLocInfo!G29
D7:D8	D7	=ProgramLocInfo!G32
D9	D9	=ProgramLocInfo!G35
D10:D11	D10	=ProgramLocInfo!G38
A3:A15	A3	=C21
D13	D13	=ProgramLocInfo!G90
D14	D14	=ProgramLocInfo!G109
D15	D15	=ProgramLocInfo!G123
A21:A33	A21	=D21
B21:B33	B21	=CELL("address",INDEX(ProgramLocInfo!$A$1:$A$141,MATCH('Programs_1-3'!B3,ProgramLocInfo!$A$1:$A$141,0)))
C21:C33	C21	=SUBSTITUTE(SUBSTITUTE(A21,"$",""),"$","")
D21:D33	D21	=RIGHT(B21, LEN(B21)-FIND("!",B21))

ProgramListRebuild.xlsm
	A
1	A
2	Program
3	7-Zip 15.14 (x64)
4	Ablebits Ultimate Suite for Microsoft Excel
5	Adobe Reader DC Ver19.021.20058
6	Agent Ransack x64
7	Aimersoft Video Converter Ultimate
8	AllMyNotes Organizer
9	Amazon Kindle
10
11	Autodesk AutoCAD 2014
12	Autodesk Revit 2014
13	Autodesk Revit 2015
14
15	Belarc Advisor
16	Bitdefender Antivirus Free
17	Bluebeam Revu x64 2015.6
18	Bookviser Reader
19	Brave Browser
20	Bulk Rename Utility 3.0.0.1 (64-bit)
21	calibre 64bit - E-book management
22	Canon CanoScan LiDE220 Manual
23	Canon Quick Menu
24	CloneSpy 3.42 - 64 bit
ProgramLocInfo

 

[Toadstool]

Hi MrDB4Excel,

I'm struggling to follow what you want but suspect A3 should hyperlink to ProgramLocInfo cell A21, but if the "calibre 64bit - E-book management" is moved to another row then the hyperlink should update.

It seems you've almost got it with B17 so does this do what you ask?

MrDB4Excel.xlsx
	A	B	C
1		Rebuild/ Reinstall: Y = Yes, N = No, M = Maybe
2		File Mgmt. Programs
3	Click	calibre 64bit - E-book management	1
4	Click	CloneSpy 3.42 - 64 bit	2
5	#N/A	Dexplorer	3
Programs_1-3

Cell Formulas
Range	Formula
A3:A5	A3	=HYPERLINK("#"&CELL("address",INDEX(ProgramLocInfo!$A$1:$A$141,MATCH('Programs_1-3'!B3,ProgramLocInfo!$A$1:$A$141,0))),"Click")

 

[MrDB4Excel]

Bravo Toadstool!!! Perfect, absolute. Works like a charm. You made my day.
Thank you so much.

This may be a big reach, but is it possible that instead of the word "Click" that the cell indicates the cell on sheet ProgramLocInfo where the indicated program occurs, even when the program indicated is moved to another row?

 

[MrDB4Excel]

Bravo Toadstool!!! Perfect, absolute. Works like a charm. You made my day.
Thank you so much.

This may be a big reach, but is it possible that instead of the word "Click" that the cell indicates the cell on sheet ProgramLocInfo where the indicated program occurs, even when the program indicated is moved to another row?

Previous reply continued... from This may be a big reach,...

In other words, for example in cell A3 the phrase "Click" is looking at the content of cell C21 and always returns the result in C21 if the program is moved to another location.
So for example, if "calibre 64bit - E-book management" is moved on sheet ProgramLocInfo to row 24 then on sheet "Programs_1-3" cell C21 would report A24 thus causing cell A3 on sheet "Programs_1-3" to report A24.

 

[Toadstool]

In cell C21 try:

=ADDRESS(MATCH('Programs_1-3'!B3,ProgramLocInfo!$A$1:$A$141,0),1,4,1)

 

[MrDB4Excel]

=ADDRESS(MATCH('Programs_1-3'!B3,ProgramLocInfo!$A$1:$A$141,0),1,4,1) works equally well as =SUBSTITUTE(SUBSTITUTE(A21,"$",""),"$",""); cell C21's result changes when the program "calibre 64bit - E-book management" is moved on sheet ProgramLocInfo to another row.
However; A3 still remains with the same result: "Click"

 

[Toadstool]

I'm not sure of the purpose from row 21 on Programs_1-3. I would suggest it can be done using the MATCH formula.

MrDB4Excel2.xlsx
	A	B	C	D
1		Rebuild/ Reinstall: Y = Yes, N = No, M = Maybe
2		File Mgmt. Programs		1
3	A21	calibre 64bit - E-book management	1	0
4	A24	CloneSpy 3.42 - 64 bit	2	0
5	#N/A	Dexplorer	3	#N/A
Programs_1-3

Cell Formulas
Range	Formula
A3:A5	A3	=HYPERLINK("#"&CELL("address",INDEX(ProgramLocInfo!$A$1:$A$141,MATCH('Programs_1-3'!B3,ProgramLocInfo!$A$1:$A$141,0))),ADDRESS(MATCH('Programs_1-3'!B3,ProgramLocInfo!$A$1:$A$141,0),1,4,1))
D3:D5	D3	=INDEX(ProgramLocInfo!G:G,MATCH('Programs_1-3'!B3,ProgramLocInfo!$A$1:$A$141,0))

 

[MrDB4Excel]

Thanks again Toadstool, your formula for A3 does the trick. The formula for D3 isn't needed, I tried it and it doesn't do anything for the purpose that I can perceive.
Column D is only a checklist for me as I determine if a program is to be reinstalled during my PC rebuild process.
Rebuild/ Reinstall: Y = Yes, N = No, M = Maybe

 

[Toadstool]

You're welcome.

I was guessing that column D in Program_1-3 might be one of many columns being pulled from ProgramLocInfo, in which case the INDEX and MATCH would be more efficient than using the volatile INDIRECT and the column A cell address.