Dynamic Filename in Linked cell

EMEZINSP

New Member
Joined
Jul 3, 2018
Messages
5
I am trying to setup a dynamically linked cell.

This is the link i have:



='https://mezcom.sharepoint.com/sites/Mez/Shared Documents/John Smith/[John Smith 2018 Time Sheet.xlsx]Jan 1-15'!$AH$8

this is what i am trying to do:

B1 would equal a persons name, b3 would be the year.

='https://mezcom.sharepoint.com/sites/Mez/Shared Documents/(=B1)/(=B1) (=B3) Time Sheet.xlsx](=A9)'!$AH$8

Then all my cells will link up to the necessary documents by only entering name and year, the rest will be done by the sheet.



the closest i have gotten is:

=indirect'https://mezcom.sharepoint.com/sites/Mez/Shared Documents/John Smith/["&B1&"] 2018 Time Sheet.xlsx]Jan 1-15'!$AH$8

But that gives me a ref error.



Any help would be greatly appreciated. Thanks
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hello,

Without the ability to test ... here would be my guess-timate ...

Code:
=INDIRECT("'https://mezcom.sharepoint.com/sites/Mez/Shared Documents/"&B1&"/["&B1&" "&B3&" "&"Time Sheet.xlsx]Jan 1-15'!$AH$8")

Hope this will help
 
Upvote 0
I have gotten further, spent a few hours experimenting.

This is where i am at:
=INDIRECT("'https://mezcom.sharepoint.com/sites/Mez/Shared Documents/"&B1&"/["&B1&" "&B3&" "&"Time Sheet.xlsx]Jan 1-15'!AH8")

It finds the file now, but it still has a ref error, so i think there may be something up with referencing the sheet and cell part: Jan 1-15'!AH8")


I know this works:
='https://mezcom.sharepoint.com/sites/Mez/Shared Documents/John Smith/[John Smith 2018 Time Sheet.xlsx]Jan 1-15'!$AH$8
the above find the file, but the indirect function breaks the sheet and cell reference part if i had to guess. if i get this working it saves me over 300 manual links per year.

Thanks in advance
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

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
Back
Top