Solved

Using indirect formula with a file path that varies.

Posted on 2016-08-17
9
78 Views
Last Modified: 2016-08-31
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
0
Comment
Question by:agwalsh
  • 5
  • 4
9 Comments
 
LVL 25

Accepted Solution

by:
ProfessorJimJam earned 500 total points
ID: 41759337
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
 

Author Comment

by:agwalsh
ID: 41759710
Would it work if the file was open?
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41759789
Yes, if the file is open then there is no need for UDF. It will work.
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:agwalsh
ID: 41759967
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
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41760110
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
 

Author Comment

by:agwalsh
ID: 41768330
OK, let me go through it again. Obviously I missed something...thanks :-)
0
 

Author Comment

by:agwalsh
ID: 41777967
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
 

Author Closing Comment

by:agwalsh
ID: 41777969
Did exactly what I wanted and the Concatenate solution was elegant. :-)
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41777971
you are welcome agwalsh. i am glad i was able to help.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question