Reference text string as external link path

[brent78]

I'd like to use a variable text string to update the path of an external link. I have the text string but am unable to get the value, it just shows the text.

For example I have files and folders named by year. I'd like to have a cell where I change the year and then another cell that joins all the text together to create the path to the new cell location.

Book1
	A	B	C	D
1	FolderYear	FileYear	FilePath	RevisedPathFormula
2	2023	2022	File1.xlsx]worksheet1'!$D$21	'C:\folder1\2023 folder\2022File1.xlsx]worksheet1'!$D$21
Sheet1

Cell Formulas
Range	Formula
D2	D2	="'C:\folder1\" &A2 &" folder\" &B2 &C2&""

 

[severynm]

Hi!

Wrap your formula inside of the Hyperlink function.

=HYPERLINK("'C:\folder1\" &A2 &" folder\" &B2 &C2&"")

 

[brent78]

This seems to jump to the location but I'd like it to display the value instead. Is there another way to do it?

 

[brent78]

Hi!

Wrap your formula inside of the Hyperlink function.

=HYPERLINK("'C:\folder1\" &A2 &" folder\" &B2 &C2&"")

This seems to jump to the location but I'd like it to display the value instead. Is there another way to do it?

 

[konew1]

="'C:\folder1\" &A2 &" folder\" &B2 &C2&""

Try putting = inside the text
"'=C:\folder1\" &A2 &" folder\" &B2 &C2&""

 

[severynm]

Oh wait - you actually want that to resolve to a reference and not just a link? Anytime you want to build up a reference from strings you'll need to use Indirect. Since you're referring to an external workbook. you must have that workbook open or the function will return #Ref! error.

Excel INDIRECT Function Ultimate Beginner Guide with Videos

Master Excel INDIRECT function with 4 practical examples. Create dynamic references, dependent drop-down lists. Videos, written steps, Excel workbook

contextures.com

 

[brent78]

Oh wait - you actually want that to resolve to a reference and not just a link? Anytime you want to build up a reference from strings you'll need to use Indirect. Since you're referring to an external workbook. you must have that workbook open or the function will return #Ref! error.

Excel INDIRECT Function Ultimate Beginner Guide with Videos

Master Excel INDIRECT function with 4 practical examples. Create dynamic references, dependent drop-down lists. Videos, written steps, Excel workbook

contextures.com

Ah, yes. Indirect fixes that and now it will work to resolve the reference. That is a bummer that workbook has to be open. That kind of defeats the purpose of what I was trying to do I was hoping to save time by just changing cells for the folder year, file year and have it automatically update. Since I have to open the workbooks anyways I might as well just create new external links.