Using indirect formula with a file path that varies.

I've attached a file using Vlookup and a file name. That works fine. If I wanted to use this formula with a specific file name (and that actually works fine). If I wanted to use a file path e.g. "C:\Users\JBloggs\Downloads" in the indirect formula, how would I do that, what would be the syntax and what are the things I should look out for? Thanks
indirect_dynamic_formula.xlsx
LVL 1
agwalshAsked:
Who is Participating?
 
ProfessorJimJamConnect With a Mentor Commented:
Indirect function cannot pull data from closed workbook.

but there are different workarounds.

you can use the built-in Indirect , but then on open or change event a macro needs to run to open the workbook from the path and update the data and then close, this way, i do not recommend.

there was another INDIRECT.EXE UDF embedded with MoreFun add-in, which is also something i do not recommend using now, in addition there was another UDF by Halan Grove called "Pull" as well, but i have never tried it.

what i recommend is the following attached solution.
there was a very good UDF Designed and written by Wilson So, which is embedded in the solution i have prepared and attached.

in the attached file EE.xlsm,  Column C is your path, column D is file name including its xlsx extention, then Column E is sheet name and Column D is range

then in column G, is concatenation of from C to D to give reference for the UDF.

download file vlookup_with_indirect_source.xlsx attached and put it in C:\Users\JBloggs\Downloads
then open EE.xlsm and see the formula there will pick up the returned value from closed workbook.
EE.xlsm
vlookup_with_indirect_source.xlsx
0
 
agwalshAuthor Commented:
Would it work if the file was open?
0
 
ProfessorJimJamCommented:
Yes, if the file is open then there is no need for UDF. It will work.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
agwalshAuthor Commented:
I tried this with the file open and with the UDF and the ordinary Indirect. I copied and pasted the path from Windows Explorer. When I used the UDF I got N/A, when I tried it with the ordinary Indirect I got REF#. I referenced the file in a folder I know exists...what could I be missing here?
0
 
ProfessorJimJamCommented:
the UDF solution only should be used when you want to retrieve from closed workbook

Did you follow the steps I described in my post with attachment? That should  definitely work.

But if you want to reference from open workbook then on the reference simply use click on the source workbook and there is no need for UDF to be used, the reference of open workbook will only show the workbook name with its range and it will not show the path but after you save the master file and close the source workbook then you open the master file you will see prompt of connection warning and also you can see full path with  workbook  name in the formula but t will not be dynamic because of you close master file and then you open source file and chage some values there and save and close and when you open master file the changes made in source file will not be updated in master file .
Therefore the use of UDF comes handy if you want any update on the source file to be reflected I. The masterfile.
0
 
agwalshAuthor Commented:
OK, let me go through it again. Obviously I missed something...thanks :-)
0
 
agwalshAuthor Commented:
I went through this again and yes, it worked perfectly this time. What I did differently was that I used the Copy Path option in Windows Explorer to get the path to the file...I tried it with a number of different files and that worked perfectly. Really liked your Concatenate solution - much more elegant than what I had :-) Thank
0
 
agwalshAuthor Commented:
Did exactly what I wanted and the Concatenate solution was elegant. :-)
0
 
ProfessorJimJamCommented:
you are welcome agwalsh. i am glad i was able to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.