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]

It is not a simpler error.

Using INDIRECT to reference data in another file will work only if the other file is open. If not, it returns a #REF! error.

The solution requires VBA. There are two or three ways to do it, but they all require VBA. I haven't used cell references to a file by using a URL in a formula so I'm not even sure that works.

 

[adkinsrb69]

I was afraid of that. I am unable to use VBA due to security constraints on the network

 

[6StringJazzer]

Here is a way that you can do it with a couple of extra manual steps.

Put this formula into a cell

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

Copy that cell
Paste Values into a target cell
Go to the target cell, click F2 then ENTER
That target cell will now have the desired formula

If you have multiple rows, you can copy and paste them all at once but you will have to go to each row to activate the cell and save it as a formula.

That's not great but it does get you there without VBA.

 

[adkinsrb69]

Here is a way that you can do it with a couple of extra manual steps.

Put this formula into a cell

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

Copy that cell
Paste Values into a target cell
Go to the target cell, click F2 then ENTER
That target cell will now have the desired formula

If you have multiple rows, you can copy and paste them all at once but you will have to go to each row to activate the cell and save it as a formula.

That's not great but it does get you there without VBA.

While that did create the TEXT value of the formula but does not evaluate to the cell value in E13


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

"='https://my.sharepoint.com/personal/ApplesJacks/Documents/Budget Duties/A987654321 - Aflora - Roses/[Budget - Roses_Master.xlsx]Main'!$E$13"
** This is what is displayed in the field (without the double quotes) but does not evaluate to the value of $525.75 (which is the cell value of E13 on the Main worksheet in the file: Budget - Roses_Master.xlsx)

 

[6StringJazzer]

What value is displayed?

 

[adkinsrb69]

What value is displayed?

This is the specific value = "='https://my.sharepoint.com/personal/ApplesJacks/Documents/Budget Duties/A987654321 - Aflora - Roses/[Budget - Roses_Master.xlsx]Main'!$E$13"

It is literate text rather than evaluating and returning the actual field value of E13 (which is $525.75).

 

[6StringJazzer]

Did you copy and Paste Values from the cell with the formula? Did you press F2 and ENTER after pasting? Is the cell format General?

 

[6StringJazzer]

Here is a demo using your formula. The result is #REF! because I used your example but I cannot access that file.

 

[adkinsrb69]

Here is a demo using your formula. The result is #REF! because I used your example but I cannot access that file.

While I am trying to do something similar but use the variables to dynamically pull the values. It appears I will have to set the extremal links explicitly instead of trying to do it dynamically.