how to use a cell to reference a file?

Fotorat

New Member
Joined
Oct 7, 2019
Messages
7
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,034
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

New Member
Joined
Oct 7, 2019
Messages
7
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

New Member
Joined
Oct 7, 2019
Messages
7
I tried this to build the file name but it didn't work


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

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,917
Office Version
365
Platform
Windows
Im afraid you cant use INDIRECT on a closed workbook. Id be looking at using VBA for this.
 

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,034
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

Well-known Member
Joined
Mar 12, 2009
Messages
2,034
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

New Member
Joined
Oct 7, 2019
Messages
7
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

New Member
Joined
Oct 7, 2019
Messages
7
="["&C2&".xlsx"&"]"&"Run Plan"&"'"&"!"

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





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!!!


<colgroup><col width="64" style="width: 48pt;"> <tbody> </tbody>
 

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,034
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
 

Watch MrExcel Video

Forum statistics

Threads
1,090,119
Messages
5,412,554
Members
403,432
Latest member
cr2141

This Week's Hot Topics

Top