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

 

[baitmaster]

welcome to the board

You could look at using the INDIRECT formula, but it's not an ideal solution. It's memory-hungry, difficult to audit, and easily broken. I think it might do what you want though

I'd be more worried about why (and I'm not sure I want to raise this) you have 120+ linked files..?

This kind of mass-processing might suggest introducing some VBA-based controls: perhaps a piece of code that trawls all files in a folder, and extracts the value from a named range. Or maybe centralising the key functionality into one master file, and then generating automated reports directly from it

 

[Fotorat]

Hi BaitMaster

Don remind me!

I have inherited this pile of bumcum - so next release I will do it in MSACCESS

But I am stuck with this for now.

would you be very kind and show me how I could use indirect please?

 

[Fotorat]

I tried this to build the file name but it didn't work


Tracker/[(=Indirect,C3,True) & ".xlsx"]Run Plan

 

[steve the fish]

Im afraid you cant use INDIRECT on a closed workbook. Id be looking at using VBA for this.

 

[baitmaster]

You need to create a text string that represents a valid Excel formula, and then use Indirect(...) on that text string

So do the following as an example:
enter something on sheet1 cell A1
enter something else on sheet2 cell A1

Enter the following text on Sheet2:

C1:E1 - "text" "formula" "result"
C2:C3 - "A1", "Sheet1"

enter the following formulas on Sheet2:

D2 =C2
D3 ="'"&C3&"'!"&C2
E2 =INDIRECT(D2)
E3 =INDIRECT(D3)

The key here is getting the symbols exactly right, so note the use of ' and ! in D3. The aim here is to return the text string 'Sheet1'!A1, which is what you'd see in a formula that looks directly at that cell from Sheet2. Use of sheet name and ' can be optional, but including them makes the formula more versatile

Once you've got this, the next thing to do is include the file name. So as before, create the direct formula, and then seek to exactly recreate the text string within that formula, and then use Indirect on that text string

 

[baitmaster]

Im afraid you cant use INDIRECT on a closed workbook. Id be looking at using VBA for this.

I forget that. It can work as a worst-case plan B but is so problematic I never use it. Probably can't even use Data > Edit links to open source files and update, because there aren't any links...

 

[Fotorat]

I forget that. It can work as a worst-case plan B but is so problematic I never use it. Probably can't even use Data > Edit links to open source files and update, because there aren't any links...

Oddly edit links is working lie magic everytime I reference a new file the linked table is automatically linked in Edit Links.

I will try the kind advice now - thanks

 

[Fotorat]

="["&C2&".xlsx"&"]"&"Run Plan"&"'"&"!"

which gives me the File name and sheet name ok--



[TABLE="width: 48"]
<colgroup><col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]

then I replace [Outside of Central UAT.xlsx]Run Plan'! with (=INDIRECT(AH2))

but I get this

heres my full formula now:

=VLOOKUP($A2,'https://tsbcloud.sharepoint.com/sites/ChangeTestingTeam/UAT Testing Collateral/1. Releases Collateral/Release 17/14. Preparation and Execution Tracker/(=INDIRECT(AH2))$A$2:$N$4,6,TRUE)

I must be close by now hopefully?

Beer tokens t who ever cracks this for me!!!


[/TD]
[/TR]
</tbody>[/TABLE]

 

[baitmaster]

What you're looking for is

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

Where AH2 contains the text

'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