HLOOKUP External Source, but use Variable in the address

[aerandir]

Hi All.
I started a new job where it heavily relies on Excel (and no one seems to know much about it anyway). So I'm stuck with no one able to help me...

I'm created a "dashboard" to collate all the information from different reporting sheets. All sheets have identical structure so it shouldn't be an issue aggregating the information in one place. I created a sheet for each project where it should placed strategically in a certain structure. For example:
Main Projects Folder
---PRJ0001
------PRJ0001-PCW.xlsx
---PRJ0002
------PRJ0002-PCW.xlsx
and so on

When I create the dashboard, I use the following HLOOKUP to search through the file fields:
=HLOOKUP($A5,'https://something.sharepoint.com/Shared Documents/ES Projects/PRJ0001/[PRJ0001-PCW.xlsx]PCW'!$A:$B,Variables!E$16,FALSE)

What I'm trying to do is to take the "PRJ0001" from a different field within the same row. I've tried & and CONCATENATE but they seem to break the "link" since the source is an external source.

Any ideas of how I can achieve this?

Thank you

 

[Micron]

I am pretty green when it comes to Excel formulas but I suspect you cannot do this without vba because of the external reference. I imagine you might concatenat the result of a range or Offset(0,?) with string portions that make up the HLookup string. That string would be evaluated with Application.Worksheet function. However, I suspect if going the code route you'd abandon the lookup altogether and just get the value you need by way of referencing the sheet and the desired cell with vba.