Reference text string as external link path

brent78

New Member
Joined
Aug 18, 2006
Messages
12
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
ABCD
1FolderYearFileYearFilePathRevisedPathFormula
220232022File1.xlsx]worksheet1'!$D$21'C:\folder1\2023 folder\2022File1.xlsx]worksheet1'!$D$21
Sheet1
Cell Formulas
RangeFormula
D2D2="'C:\folder1\" &A2 &" folder\" &B2 &C2&""
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

severynm

Active Member
Joined
Jan 8, 2021
Messages
340
Office Version
  1. 365
Platform
  1. Windows
Hi!

Wrap your formula inside of the Hyperlink function.
Excel Formula:
=HYPERLINK("'C:\folder1\" &A2 &" folder\" &B2 &C2&"")
 
Upvote 0

brent78

New Member
Joined
Aug 18, 2006
Messages
12
This seems to jump to the location but I'd like it to display the value instead. Is there another way to do it?
 
Upvote 0

brent78

New Member
Joined
Aug 18, 2006
Messages
12
Hi!

Wrap your formula inside of the Hyperlink function.
Excel Formula:
=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?
 
Upvote 0

severynm

Active Member
Joined
Jan 8, 2021
Messages
340
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

brent78

New Member
Joined
Aug 18, 2006
Messages
12
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.
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.
 
Upvote 0

Forum statistics

Threads
1,186,389
Messages
5,957,566
Members
438,310
Latest member
rocketemm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top