External excel file, retrieve value, but change NAME in the URL based on cell B3

I have an excel file on sharepoint called share
I have an excel file locally stored called local

The local file has a formula that checks if a specific cell value in the share file is X or Y.
The name of the employee is located in cell B3

company.sharepoint.com/sites/SubDomain/Shared[/url] Documents/Projects/TEST/[TEST-FY-",B3,".xlsx]July'!$V$7"))

I tried CONCATE but it doesn't work. While the URL produced is correct, it doesn't actually load the value.

Is this even possible?

You can achieve this using an indirect formula. Indirect allows you to piece together a formula that can have parts that are references to other cells and/or specific information combined together. I use them pretty regularly on my spreadsheets. There are different ways to use them. =indirect("$C"&"26") will pull whatever is in $C26. If you use =indirect($C$1&$C$2) and then put $C (or C) in c1 and 26 in C2 you can control the formula by changing what is in C1 and C2. I included a sheet with examples.
Good Luck.
Squeaky

Originally Posted by Squeaky

You can achieve this using an indirect formula. Indirect allows you to piece together a formula that can have parts that are references to other cells and/or specific information combined together. I use them pretty regularly on my spreadsheets. There are different ways to use them. =indirect("$C"&"26") will pull whatever is in $C26. If you use =indirect($C$1&$C$2) and then put $C (or C) in c1 and 26 in C2 you can control the formula by changing what is in C1 and C2. I included a sheet with examples.
Good Luck.
Squeaky

Hi Squeaky,

Thank you for the reply. I checked and it works only for internal references. I couldn't make it work with SharePoint Excel reference.