Solved

Using indirect formula with a file path that varies.

Posted on 2016-08-17
9
167 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 26

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 26

Expert Comment

by:ProfessorJimJam
ID: 41759789
Yes, if the file is open then there is no need for UDF. It will work.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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 26

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 26

Expert Comment

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
converting excel into labels 6 36
Calculating Sales Tax 13 66
sending mail each sheet to respective DL with a message prompt 3 45
Excel Auto-Complete lines 4 31
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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 demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

739 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