Solved

Using indirect formula with a file path that varies.

Posted on 2016-08-17
9
96 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
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!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

765 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