FAO Excel Nerds

894 Views 17 Replies 6 Participants Last post by BRABUS, Feb 1, 2010

Tyzer

Discussion Starter · Jan 28, 2010

I've tried Googling this to no avail (or maybe I'm not looking closely).

Is it possible to have another spreadsheet as the source data in a VLOOKUP? I.E. linking to the source data spreadsheet in the VLOOKUP so that the spreadsheet containing the source data hasn't got to be open for any other VLOOKUPS in the same column to be returned?

Thanks

1 - 18 of 18 Posts

betsy!

· #2 · Jan 28, 2010

It is possible, some of my spreadsheets take data from another spreadsheet that isn't open.

However I didn't write them and don't know how to!

Hopefully someone will be along to explain for you.

Tyzer

Discussion Starter · #3 · Jan 28, 2010

betsy! said:
It is possible, some of my spreadsheets take data from another spreadsheet that isn't open.

However I didn't write them and don't know how to!

Hopefully someone will be along to explain for you.

Cheers

i've actually just found a formula that an old colleague used and I've tried using that as a guide but it doesn't play ball.

betsy!

· #4 · Jan 28, 2010

If no one replies by later and I'v got a spare five minutes, I'v got an excel formula etc book at home I'll have a nosy in.

betsy!

· #5 · Jan 28, 2010

='P:\RAWS\[New General Usage - Current Version.xls]Next Week'!$D$8

Right that's the formula that I'v got, the first part referring to the spreadsheet that I'm pulling info from, the last bit referring to the tab in the spreadsheet and the column references.

Tyzer

Discussion Starter · #6 · Jan 28, 2010

betsy! said:
='P:\RAWS\[New General Usage - Current Version.xls]Next Week'!$D$8

Right that's the formula that I'v got, the first part referring to the spreadsheet that I'm pulling info from, the last bit referring to the tab in the spreadsheet and the column references.

Yeah I have been using =VLOOKUP($C2,'W:\Monitoring Officers\[contacts database.xls]Sheet1'!$A:$Z,2,FALSE) as guide but the path for the VLOOKUP I want to use is different and so is the document name, I've tried sticking the changes in but nothing good happens :lol:

betsy!

· #7 · Jan 28, 2010

Hmmm I dunno then, the one I posted should work by changing the directory name etc. to suit where your info is stored.

The one you posted above has a fair few differences to mine.

Salamandertome

· #8 · Jan 28, 2010

You start getting into spreadsheet hell when you link them together! If you are using the drive name W:\, you need to be sure that the location will always have the same name wherever the spreadsheet is opened. And if w:\ is on a server, that it will always be accessible.

Tyzer

Discussion Starter · #9 · Jan 28, 2010

Salamandertome said:
You start getting into spreadsheet hell when you link them together! If you are using the drive name W:\, you need to be sure that the location will always have the same name wherever the spreadsheet is opened. And if w:\ is on a server, that it will always be accessible.

I've told my manager exactly that when she asked me to do this and I got the "don't question my order" look

madhouse

· #10 · Jan 28, 2010

Break it into each bit and make sure it's correct. The data you're looking up is in column A? (have done that before!)

Have you tried hitting the fx button and swing what the formula thingie has to say?

BRABUS

· #11 · Jan 28, 2010 (Edited)

This is the correct format

=VLOOKUP(A1,'[C:\Documents\Excel\June2009\myfile.xls]Sheet3'!$A$1:$G$100,4,0)

it doesnt always work with files that are not open. Well not properly and is a known issue. There is an add in feature that you can install into Excell and that will sort it for you.

I can remember what the add in is called. I will have a look on my work computer and seeif I still have it on there.

BRABUS

· #12 · Jan 28, 2010 (Edited)

I have found the addin now

This is the one you will need to download and install, its a free addin called Morefunc.dll from here: http://xcell05.free.fr/english/ and use Indirect.Ext()

e.g. =Indirect.Ext("'C:\My Documents\[SalesWorkbook.xls]"&A2&"'!$X$9") copied down.

of course, replacing path and workbook name (in italics) with actual workbook path. Watch the single and double quotes... don't delete those.

It does work and I have used it in the past to do vlookups on over 120 closed files. Try not to do that many files :lol:

Tyzer

Discussion Starter · #13 · Jan 28, 2010

BRABUS said:
I have found the addin now

This is the one you will need to download and install, its a free addin called Morefunc.dll from here: http://xcell05.free.fr/english/ and use Indirect.Ext()

e.g. =Indirect.Ext("'C:\My Documents\[SalesWorkbook.xls]"&A2&"'!$X$9") copied down.

of course, replacing path and workbook name (in italics) with actual workbook path. Watch the single and double quotes... don't delete those.

It does work and I have used it in the past to do vlookups on over 120 closed files. Try not to do that many files :lol:

Cheers, I can't install files at work without asking permission, I'll ask tomorrow.

If not I'll tell my manager it's not worth the hassle, the vlookup works fine with the lookup data in the spreadsheet as a tab.

BRABUS

· #14 · Jan 28, 2010

Tyzer said:
Cheers, I can't install files at work without asking permission, I'll ask tomorrow.

If not I'll tell my manager it's not worth the hassle, the vlookup works fine with the lookup data in the spreadsheet as a tab.

Vlookup will work fine on other tabs, its when you use it on closed workbooks that it can give the wrong results. I spotted the errors and so dug out this add-in. I can't install them either normally but the time savings were tremendous so they allowed me to do it after some careful checking of what the add-in does. Lets just say it has now been used on stuff cleared at very high MOD levels so I would consider it safe to use

andi_79

· #15 · Jan 29, 2010

Hi,

VLOOKUP should be fine on a closed workbook, you should only need to use the add in sugested if you are building the workbook path from text in other cells (as its the INDIRECT function that does not work on closed workbooks). If you are hard coding the path there shouldnt be a problem its just a case of getting the vlookup formula correct.

Hope that helps!
Andy

BRABUS

· #16 · Jan 29, 2010

andi_79 said:
Hi,

VLOOKUP should be fine on a closed workbook, you should only need to use the add in sugested if you are building the workbook path from text in other cells (as its the INDIRECT function that does not work on closed workbooks). If you are hard coding the path there shouldnt be a problem its just a case of getting the vlookup formula correct.

Hope that helps!
Andy

I totally disagree and stand by what I said previously.

andi_79

· #17 · Feb 1, 2010

BRABUS said:
I totally disagree and stand by what I said previously.

Fair enough, I've been wrong before!

Just had a quick google & tested it & it seems to work for me.

http://www.mrexcel.com/forum/showthread.php?t=71020
http://www.mrexcel.com/forum/showthread.php?t=8958

Andy

BRABUS

· #18 · Feb 1, 2010

I am not saying it doesnt work. I t does most of the time, but sometimes it will return spurious answers.

I only noticed because it returned a very very wrong figure that blew a forecast budget. When I looked further into I found out what was wrong and put some error checking in. Every so often it would return wrong figures. Not everyone spends the effort to put error checking in to their data.

Personally I would rather use a method that so far I have found to be 100% accurate.

1 - 18 of 18 Posts

This is an older thread, you may not receive a response, and could be reviving an old thread. Please consider creating a new thread.