Pull value from another excel file (without opening)

[adkinsrb69]

While I am baffled beyond belief and I am sure it is a simple error. However, I am attempting to reference a specific field in another excel file (different folder).
I am using a dynamic folder structure using variables.

So lets say I want to pull the value from cell E13 on the MAIN worksheet in a file but not hard coding the filename.

Example: (This will work if NOT using variables)
='https://my.sharepoint.com/personal/ApplesJacks/Documents/Budget Duties/{CLIENT ID} - {Client Name} - {Project}/[Budget - {Project}_Master.xlsx]Main'!$E$13

However, my attempt to use variables like this using the INDIRECT function and each {} actually references a specific cell:
=INDIRECT(CONCATENATE("'https://my.sharepoint.com/personal/ApplesJacks/Documents/Budget Duties/",{CLIENT ID}," - ",{Client Name}," - ",{Project},"/[Budget - ",{Project},"_Master.xlsx]Main'!$E$13")

Example2:
=INDIRECT(CONCATENATE("'https://my.sharepoint.com/personal/ApplesJacks/Documents/Budget Duties/",A1," - ",B1," - ",C1,"/[Budget - ",C1,"_Master.xlsx]Main'!$E$13")

Where everything in { } are variable.
I am about to pull my hair out as I can do it IF the files are both opened at the same time but that is not my goal.

HELP!!

 

[6StringJazzer]

While I am trying to do something similar

I demonstrated how to do exactly what you described, with your own example. How is this is different? The limitation is that you can't do it automatically without VBA so it requires an extra manual step.

 

[adkinsrb69]

I demonstrated how to do exactly what you described, with your own example. How is this is different? The limitation is that you can't do it automatically without VBA so it requires an extra manual step.

So my goal was to be more dynamic that I would only enter data in Columns A, B, and C and then it would dynamically put the values but based on your input, it would require additional steps to have a column that creates the linking values. To which I would have to COPY that and Paste Values into the ultimate field where the value would be.

So in Columns A, B, C are manual entries, then I would have dynamic data in Columns D - J (using the dynamically generated formulas (similar on Row 1 but referencing the proper file/worksheet/columns).

I think the end result is it will require VBA or perhaps MANUAL involvement for each column (to capture the proper link) once a new client is added.

 

[6StringJazzer]

Correct. VBA or manual.