how to use a cell to reference a file?

[Fotorat]

Hi all

I have a 2 workbooks set up s parent and chid - and a formula to pull values into the parent from the child:

=VLOOKUP($A2,'https://tsbcloud.sharepoint.com/sites/ChangeTestingTeam/UAT Testing Collateral/1. Releases Collateral/Release 17/14. Preparation and Execution Tracker/[Outside of Central UAT.xlsx]Run Plan'!$A$2:$N$4,5,TRUE)

Where I have the file name "Outside of Central UAT.XLSX" - (child) I would like to populate this from a cell in the parent (as I have 120 children in total.

I am sure it must be easy?

thanks

 

[Fotorat]

That makes sense ***thanks*** but I am now getting #REF ! and I cant figure out why!

 

[steve the fish]

You will be losing the initial ' if you type that as the first character in a cell.

 

[Fotorat]

You will be losing the initial ' if you type that as the first character in a cell.

Thanks Steve

looking for more info on my first attempt with INDIRECT

=VLOOKUP($A2,INDIRECT(AH2),5,TRUE)

and no matter what I try on AH2 I always get #REF !

Can someone clarify the correct phrasing for my AH2 :

https://tsbcloud.sharepoint.com/sites/ChangeTestingTeam/UAT Testing Collateral/1. Releases Collateral/Release 17/14. Preparation and Execution Tracker/[Outside of Central UAT.xlsx]Run Plan'!$A$2:$N$4

 

[baitmaster]

Your problem is probably that the source file is closed

To reiterate:
Create the reference to the external file, in the way that you normally would - this gives you the direct text string
Remove the = in order to convert the reference string to simple text, i.e. not a formula. Beware that a leading ' will disappear when you do this, so you'll need to put it back in, perhaps using a new formula
Once you have the text string exactly as it should be, the Indirect should work WHEN the file is open. When you close it, the result converts to #REF !

 

[steve the fish]

As i was trying to say in post number 12 if you type anything into a cell with a leading ' excel will use that ' to think you mean this is text and ignore it. Therefore your indirect will fail as it isnt a valid range. If you look at your post 13:

https://tsbcloud.sharepoint.com/site...estingTeam/UAT Testing Collateral/1. Releases Collateral/Release 17/14. Preparation and Execution Tracker/[Outside of Central UAT.xlsx]Run Plan'!$A$2:$N$4

has a missing leading '. You therefore need to include it in your indirect which is nothing more than a string that points to your range. Try this:

=VLOOKUP($A2,INDIRECT("'"&AH2),5,TRUE)

You will then get reference error that will correct when you open the appropriate workbook. Are you sure you want to use a VLOOKUP with TRUE at the end??