?
Solved

How can I refer to a workbook on a different disk and location without the program asking me what the filename is?

Posted on 2011-10-23
14
Medium Priority
?
394 Views
Last Modified: 2012-05-12
I have a spreadsheet that needs to get a piece of data from a large worksheet on another disk.  I am using vlookup to find that value.  In the Vlookup I refer to the other file in the second argument as

's:\[&$B$28&]sheet1'!$A$2:$U$16000   Where B28 contains a string with the filename holding the data.

This works but every time I make a change when Vlookup is executing it pops up a dialogue box asking me to update the values for $b$28.  It shows the files in the right directory but asks me to click on the proper file containing the data.  Any way to avoid this?  It seems to me I am giving excell all the informaiton it needs to find the file.
0
Comment
Question by:dma70
  • 7
  • 6
14 Comments
 
LVL 35

Expert Comment

by:Norie
ID: 37014732
Have you tried using the UNC path instead of the name of the mapped drive?

That's assuming this is on a network of course.
0
 
LVL 1

Author Comment

by:dma70
ID: 37014988
I think the mapped drive does work.   Here is why -- When I explicitly name the location of the file with the mapped drive (that is s:\ -- I assume this is what you mean by mapped) stated explicitly.  The spreadsheet finds the data no problem.   It does not pop the dialogue box up to verify the location of the file at all.    However any time I try to soft code any part of the file name it doesn't get the info.   Somehow excel won't translate the filename by reference to a string in another cell.  The filename where my data resides changes every day and the file name it is stored in had the date in its name.  So I need softcoding flexibility so when the spreadsheet updates it looks to a new file to find the data.    Seems there most be a way to softcode the file name.  If you can give me an example with a softcoded filename that would help - or tell me it just can't work.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37016219
I believe you need to use the INDIRECT function:

=Vlookup(A2,INDIRECT("s:\[" & $B$28 & "]Sheet1'!$A$2:$U$16000"),column,FALSE)

Where A2 is pointing at what you're trying to look up, column is the column number, and FALSE for exact match.

See: http://www.contextures.com/xlFunctions05.html

The file you're referring to must be open, for INDIRECT to work.

However, there's a utility out there that will work for you on closed files, see MOREFUNC, which is a library of great functions, including INDIRECT.EXT which will allow you to make this type of reference on closed files:  http://xcell05.free.fr/morefunc/english/index.htm#Functions

Cheers,

Dave
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:dma70
ID: 37021020
Dave:  i will try this out -- I do prefer not having the file open.    Just out of curiosity, what is the reason that a softcoded reference to the file name does not work?
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37021064
I was just as surprised, way back when, after leaving the LOTUS 123 world for Excel and found you couldn't construct formulas in this way without the help of INDIRECT.

If you don't literally type a reference in, Excel will not interpret it correctly, without the use of the INDIRECT command.

example:

=VLOOKUP(A2,A2:B21,2)  <- this will work

=VLOOKUP(A2,"A2:B21",2) <- this will not work

=VLOOKUP(A2,INDIRECT("A2:B21"),2) <- this will work
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37021085
@dma70 - I assume you've tested this with the file open and find that INDIRECT works for you, is this correct?  If so, the move to INDIRECT.EXT with the MOREFUNC utilities should be a breeze.

Let me know if further assistance is needed.

Dave
0
 
LVL 1

Author Comment

by:dma70
ID: 37021102
it may take me a few days -- I am at a conference today and tomorrow.  As soon as I test I will accept this as a solution - thanks for you patience  -- ps you are dating yourself mentioning LOTUS 123  I would guess 1/2 the people on this sight don't even know what that is.
0
 
LVL 42

Accepted Solution

by:
dlmille earned 750 total points
ID: 37021116
hehehe - Stand back in awe!  I wrote 123 macros in 1986, lol

Here's more on INDIRECT from Chip Pearson's website.  Its a pretty powerful function:  http://www.cpearson.com/excel/indirect.htm

Dave
0
 
LVL 1

Author Comment

by:dma70
ID: 37089033
Dave:  Seem to have figured this out - with some serious testing.   The file does indeed need to be open for the indirect function to work.  However you do not need the full pathname only the filename in []  - it finds the data in any file of that name that is open at the time.   I would really like to not have to have the file open at the time -but I found your reference for unopened files confusing.

Also I got sidetracked because in your initial example you had a unmatched '  after Sheet1 which kept the indirect function from working.

This really shouldnt be so hard -- thanks for your help
0
 
LVL 1

Author Closing Comment

by:dma70
ID: 37089041
confused on the need to have the full pathname provided.  and there was an unmatched ' in the example which caused extra debugging time.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37089215
Its not hard, but to the uninitiated (and I was uninitiated to INDIRECT at one time, not too long ago) it can be, until one has his/her first success.

You were given several links to experts in this arena - re: indirect function.  Also, you were given the link to the INDIRECT.EXT function which enables using INDIRECT with the file closed.

You might recall your initial question had:

's:\[&$B$28&]sheet1'!$A$2:$U$16000  

As the example you needed help on.  I merely showed you how to use that same parameter, with the indirect function.

Just like a file link, you WILL need full pathname to the external file, UNLESS the external file is ALWAYS in the SAME path as the spreadsheet referencing it.  I'm a bit lost by your comment as I'm not sure we ever discussed whether the full path was needed or not.

I must have miscopied your original text, losing the initial ' I guess that's why you felt a B rating was appropriate.  Ok, I accept your input and will use it in the spirit I believe it was given - not to punish, but to improve the achievements of those you touch.

I'm glad you now have a solution that works.  I'm glad you've learned about INDIRECT and that you'll be using it in the future as you need it.  I'm also glad you're knowledge has been enhanced on the MOREFUNC utilities which allow INDIRECT to be used with files that were closed.

So, you have a great day, and I hope you remember this learning event every time you use these functions, as I will remember your constructive input.

Peace,

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37089220
Reading your comments further, I take it you didn't try the INDIRECT.EXT function.  I'd encourage you to look at that, because it allows you to do what you need to WITHOUT having the file open.

Also, it appears perhaps another Expert might have suggested full UNC path, not I, though my comments on path, above, do stand - and that's because I'd thought you were trying to access files that were closed.

Cheers,

Dave
0
 
LVL 1

Author Comment

by:dma70
ID: 37089225
thanks for your understanding.  I will have to do some digging on using INDIRECT when the files are closed.  At this point I got what I needed for the moment.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37089228
Feel free to post back here, if you have any difficulties with it, setting it up, or you're losing time debugging - we can help.

:)

Dave
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

807 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