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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
 
Upvote 0
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?
 
Upvote 0
I tried this to build the file name but it didn't work


Tracker/[(=Indirect,C3,True) & ".xlsx"]Run Plan
 
Upvote 0
Im afraid you cant use INDIRECT on a closed workbook. Id be looking at using VBA for this.
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
="["&C2&".xlsx"&"]"&"Run Plan"&"'"&"!"

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



DvgvhURdEMeo8JgAAAAASUVORK5CYII=


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

but I get this
A6FcVlj1etThAAAAAElFTkSuQmCC


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>
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top