VLookup in other document

Here is my problem.  Please help 

I have two documents A and B. 

In document A  I have a table existing of codes and descriptions
Range A1: B100  Column A contains codes, Column B contains
descriptions


In document B I want to lookup the description for a given code. 
I have a cell C10 containing the code and a cell C11 containing a
formula
=VLOOKUP(A1;'K:\Douane\Regressie\[RTScenario.xls]FxP'!$A$1:$B200;2) 
Till so far it works !!

But I use this formula many times in my excel sheet and in many Excel
documents, so if Ithe lokation of document A changes, I have to change
all the formula's in all the documents. 

So I want to put the lokation in a named field [location]
M2 = 'K:\Douane\Regressie\[RTScenario.xls]FxP'!$A$1:$B200
And change the formula to formula =VLOOKUP(A1;location;2)

However this does not work.  The location is not recognised. 
What can be wrong ?   Are there other solutions for this problem. 

Thanks a lot 
Dave



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

0
10/2/2003 9:36:01 PM
excel 39879 articles. 2 followers. Follow

1 Replies
1001 Views

Similar Articles

[PageSpeed] 37

I think you'd want to use =indirect() in your formula.  But the bad news is that
indirect() won't work with a closed workbook.

But it might even be just as easy to open your workbook (choose not to update
links if you want) and then Edit|Links to change your links to point to a
different workbook.



Davwe wrote:
> 
> Here is my problem.  Please help
> 
> I have two documents A and B.
> 
> In document A  I have a table existing of codes and descriptions
> Range A1: B100  Column A contains codes, Column B contains
> descriptions
> 
> In document B I want to lookup the description for a given code.
> I have a cell C10 containing the code and a cell C11 containing a
> formula
> =VLOOKUP(A1;'K:\Douane\Regressie\[RTScenario.xls]FxP'!$A$1:$B200;2)
> Till so far it works !!
> 
> But I use this formula many times in my excel sheet and in many Excel
> documents, so if Ithe lokation of document A changes, I have to change
> all the formula's in all the documents.
> 
> So I want to put the lokation in a named field [location]
> M2 = 'K:\Douane\Regressie\[RTScenario.xls]FxP'!$A$1:$B200
> And change the formula to formula =VLOOKUP(A1;location;2)
> 
> However this does not work.  The location is not recognised.
> What can be wrong ?   Are there other solutions for this problem.
> 
> Thanks a lot
> Dave
> 
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~ View and post usenet messages directly from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
10/2/2003 11:04:36 PM
Reply:

Similar Artilces:

Accessing Document class from CPropertySheet
How do I access the document from a Property Sheet/Property Page or Dialog? William, > How do I access the document from a Property Sheet/Property Page or Dialog? Pass document's pointer in your property sheet class's constructor. -- Regards, Kobi Ben Tzvi "William Gower" <w_gower@hotmail.com> wrote in message news:ufVxpphgDHA.1932@TK2MSFTNGP11.phx.gbl... > How do I access the document from a Property Sheet/Property Page or Dialog? > > ...

contacts not showing when typing a new document
We have a win2000 domain, running xchange 5.5 and using citrix for our clients to log on. One is having a problem with her contacts. When in outlook 2000 i click on her contacts, I can clearly seem them, there must be around 80-100 in it, but when you go to type a new e-mail, click on To, drop the "Show Names from the:" and select Contacts, nothing shows. I even copied the Contacts folder to another one, made sure that "Show this folder as an address book" is ticked, on both, still nothing! It's puzzling, it started happening recently. Because we have 3 different serve...

How can I go to a specific page number in a large document
How do I go to a specific page number in a large word document. Read the replies to your earlier identical post -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> "yisaajao" <yisaajao@discussions.microsoft.com> wrote in message news:CFC4289D-FC93-4C1B-951B-DB08CED833...

vlookup's value
HI, Please help I use vlookup, but it return a "#N/A", please show me how to make it be zero, because I want to use it to plus a cell Thanks in advance Monke =IF(ISNA(VLOOKUP(B2,A2,1,FALSE)),0,VLOOKUP(B2,A2,1,FALSE)) You have to do this as the VLOOKUP errors so to get "0" when it error you have to trap it with an IF statement Dunca -- Message posted from http://www.ExcelForum.com ...

failed to create empty document?
hi all, i created a mfc based SDI application. it compiles successfully. But when i try to run it a messges box shows "failed to create empty document" what's that? what to do? plz help. thanks & regards manikandan.r "Manikandan" <manikandan_r@hotmail.com> wrote in message news:OsoOd2DXFHA.2776@TK2MSFTNGP12.phx.gbl... > hi all, > > i created a mfc based SDI application. > > it compiles successfully. > > But when i try to run it a messges box shows "failed to create empty > document" what's that? > > what...

vlookups sheet is too big
Group, I created a sheet a while back with Vlookups all over the sheet which is currently 14 Megs im trying to get the sheet down to a reasonable size. Does anybody know of a way to do this. Thanks in advance, Fish Fish Perhaps Excel estimates your actual range of data as far larger than it really is. To reset the "used range" http://www.contextures.on.ca/xlfaqApp.html#Unused Gord Dibben Excel MVP On Mon, 8 Nov 2004 09:34:17 -0800, "Fish" <anonymous@discussions.microsoft.com> wrote: >Group, > >I created a sheet a while back with Vlookups all over...

Merging three publisher documents into one
How do I merge three Publisher documents into one master document? All three are in the same format. Copy and paste. -- JoAnn Paules MVP Microsoft [Publisher] "jo" <jo@discussions.microsoft.com> wrote in message news:91582EF0-7004-4BF3-9F40-1B82AE85C1BC@microsoft.com... > How do I merge three Publisher documents into one master document? All > three > are in the same format. ...

VLOOKUP for ZIP codes??
Hi all. I work for a college and we've divided up the country, by zip code, into many many many different regions. What I have is a worksheet in excel that looks like this 'MinZip' 'MaxZip' Region 75000 75899 DALLAS 76000 76899 DALLAS Where MINZIP s the beginning number of a ZIPCode range for a particular region, and MAX is the terminating zip code. On a separate form, I have the listing of our applicants and their zip codes. I was to create a lookup formula that says (in english): If REALZIP is between MINZIP and MAXZIP on row one, re...

copy formula, VLOOKUP
Unable to copy formula to other cells =VLOOKUP(A2,Sheet2!$A$1:$B$4,Sheet2!$A$1:$A$4,FALSE). I have trie everything I can think of to copy this formula. Thank yo -- Message posted from http://www.ExcelForum.com The third argument should be the column number from which you want to return the result. Since your range is only two columns, your formula could be: =VLOOKUP(A2,Sheet2!$A$1:$B$4,2,FALSE) BRustigian < wrote: > Unable to copy formula to other cells, > =VLOOKUP(A2,Sheet2!$A$1:$B$4,Sheet2!$A$1:$A$4,FALSE). I have tried > everything I can think of to copy this formula. ...

How to use VLookup with a range of numbers?
Hi I'm trying to solve this problem I have and could use some help, I was wondering if its possible to use VLookup to check through a range of data in one cell, ex: 89-100 or 71-74. But I'm not sure if there is a way to make excel understand FROM 89 TO 100 0r 71 TO 74. (Yes I've just started learning excel on my own) This is the problem I'm trying to solve: To calculate the data in the 20 Point Scale column, enter a formula that converts each student’s Final Percentage to a number grade on the 20 Point Scale as outlined below:- 80% and above = 1 75-79% ...

vlookup formula question #2
For the formula below, =IF(ISNA(VLOOKUP(A1,Sheet2!A:A,1,FALSE)),"no","yes") What is "ISNA" and what does each value separated with comma represent? Thanks in advance! -- Message posted via http://www.officekb.com Hi Janice not meaning to be unhelpful here, but you should check Help out for a good explaination of both of these questions. Depending on your version - ISNA is probably listed under a page called IS FUNCTIONS and VLOOKUP should come up if you type it into the paperclip (office assistant) or the ask a question box. Alternatively using the Pas...

Excel adds 1 to the title of my document
This is a multi-part message in MIME format. ------=_NextPart_000_000F_01C3DC33.7D9B5300 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Why is it that when I open up a document up in Excel it adds a 1 after = the title? Then when I=20 go to save it, it doesn't want to save to the document that I want. ------=_NextPart_000_000F_01C3DC33.7D9B5300 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML&g...

converting excel 2002 files into PDF documents for emailing
I'm trying to convert Excel files that I've done before, but are now on my server versus the desktop and now I cannot convert these files to PDF's even though the Adobe Acrobat software is also on the server and I have numerous files I need to email out as PDF's. Any help will be greatly appreciated. ...

not the usual "A document with name _____.xls" problem
I have a Word document that has links to an Excel spreadsheet. When I open the Word document and tell it to update the links, I get the "A document with the name _____.xls is already open" error message. It only happens with this one Excel workbook, and only when I open the Word document and ask it to update the links. I don't get the message when I open the Excel workbook. I don't have the workbook open when I attempt to open and update the Word document. I've verified that the "Ignore other applications" check-box is not checked. I have done the "excel...

I wanted to convert my Excel sheet to a word document format.
I wanted to convert my excel sheet to a word document format. If there is a way I could this or you have another question for me please send email too Mbarbine@adelphia.net I never use this account ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.micros...

Access 2003 mail merge fails to recognise a mail merge document.
I am trying to merge a single record in an Access table into a Word document. I have set the data source in the Word document, and when I open it, it correctly displays the record from Access. However, I would like to be able to open the document programmatically from within Access, with this code: Set wrdDoc = wrdApp.Documents.Open("doc name", , True) wrdDoc.MailMerge.Execute But the Execute property generates the error: This method or property is not available because the document is not a mail merge main document. The document opens but does not display the right record. ...

Outlook 2007
We email .pdf documents around the office as attachments to Outlook messages. Users are not diligent about deleting their email and our email stores are enormous. Is there a way to email a link to a network document, instead of attaching the actual document? I am hoping that being able to do so would save space on our server. Erica <eheth@bgrtriallaw.com> wrote: > We email .pdf documents around the office as attachments to Outlook > messages. Users are not diligent about deleting their email and our > email stores are enormous. Is there a way to email a link to a > ne...

unapply AP documents
Hi, Can anyone share who to unapply a credit (or payment) to an AP invoice after it's already been posted? I'm on GP 9.0 and when I look up how to do this in the Help menu it says to go to: Transactions >> Purchasing >> Apply To. I don't have the "Apply To" option on my menu (I have full rights to everything) Any suggestions are helpful. We figured a workaround by inputting an invoice but if there's a better way, then we would love to know. Thank you Hi lilyb: The path is Transactions>Purchasing>Apply Payables Documents. There is an 'Un...

copy scanned documents to excel
Hello, please help, I recieve a lot of documents in paper and I need to input them to excel, i scann them with imaging but it comes out single columned and that doesnt work for me, what can i do? Have tried the Text to Column command, under Data? It's function is to split up data in such situations. If you're not familiar with it, try following the Text to Column wizard (the screen that appears when you use Text to Column). "Malu" <Malu@discussions.microsoft.com> wrote in message news:42B2C174-B169-4BC1-9522-8EA78E0AE9BD@microsoft.com... > Hello, please hel...

How can I stop my computer saving documents as Read Only?
I find it irritating when files save as Read Only. If I edit them I have to save again under another name. Then the original file can be neither moved or deleted. How can I stop files being saved as Read Only? "StellaB" wrote: > I find it irritating when files save as Read Only. If I edit them I have to > save again under another name. Then the original file can be neither moved > or deleted. How can I stop files being saved as Read Only? Make sure the file is not protected. Under the Review tab under Protect Document make sure it is Unrestricted...

Vlookup hyperlink
Dear All I am using Office 97 :( And want to try to do a vlookup but the value I want to retrieve is a hyperlink, when doing this it returns the text but the hyperlink doesn't work Is there anyway this can be done Thank Adam Hi Adam try something like =HYPERLINK(VLOOKUP(....)) -- Regards Frank Kabel Frankfurt, Germany Adam wrote: > Dear All, > > I am using Office 97 :( And want to try to do a vlookup but the value > I want to retrieve is a hyperlink, when doing this it returns the > text but the hyperlink doesn't work. > > Is there anyway this can be done? ...

Help Please with Dropdown lists and Vlookup
How can I integrate using a multiselect dropdown list and vlookup function? I have gone to "Contextures" website but do not see this type of example. I am trying to setup a form with a couple of list boxes where if I select the first box and select an item(s) they will be filled in the adjoing cell and the other list boxes will be populated with the data pertaining to the item selected in the first list box. Any help or reference to any site will be greatly appreciated. Hi Totally Confused, You say that you have looked at the Contextures site, but did you look at Debra Da...

Naming documents
I am trying to explain to a user why names of word documents should not contain periods; for example they want to name a document: memo from enf dir. to caf dir. about mvd div. I understand why - but I can't find something in print to help me explain why. Thanks. I don't think that there's a technical reason why you shouldn't use dots in the file name. Personally, I try to only use dots to separate the name from the extension. (I'll use underscores instead of dots.) memo_from_enf_dir_to_caf_dir_about_mvd_div.doc (I don't like spaces in my filenames, either--ag...

get rid of #N/A in a VLOOKUP
I need to replace the #N/A values to be blank within a VLOOKUP formula. How do I do this? I've tried the following but excel doesn't seem to like it: =IF(ISBLANK(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE),"",(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE)) Hi, You are missing a closing parens in the formula and I usually use ISERROR. =IF(ISERROR(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE)),"",VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE)) HTH -- Ken Hudson "AZExcelNewbie" wrote: > I need to replace the #N/A values to b...

How do I write in both columns when I format my word document?
I am typing a document in word and I need two columns formatted with text in both. I am only able to write in the left column and cannot type in right column. Please help. With "newspaper" type columns, you need to insert a column break to move from the first to the second column. (Page Layout tab>Breaks in Page Setup section of Ribbon>Column) If you want to switch backwards and forwards, maybe you should be using a two column table instead. If you go this route, insert new rows every now and then as exceptionally long rows (spanning pages) can be a bit of an...