Solved

Using indirect formula with a file path that varies.

Posted on 2016-08-17
9
54 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
 

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now