Dynamic Filename in Linked cell

[EMEZINSP]

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

 

[James006]

Hello,

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

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

Hope this will help

 

[EMEZINSP]

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