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
918 Views

Similar Articles

[PageSpeed] 57

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:

what is best way to convert a Publisher document to PDF?
I prepare several newsletters usng Publisher. Printers would prefer getting PDF file rather than hard copy. Can you recommend reliable, economical software to convert a Publisher doc to a PDF file? Acrobat (the creator not the reader...$$$) PrimoPDF CutePDF "RichMac" <RichMac@discussions.microsoft.com> wrote in message news:3BAD3484-64BA-42D7-99CA-C5427284D9EE@microsoft.com... |I prepare several newsletters usng Publisher. Printers would prefer getting | PDF file rather than hard copy. Can you recommend reliable, economical | software to convert a Publisher doc to a PDF f...

word document 02-20-10
I am writing a paper for school and clicked on refrence and intext citation to help cite my paper and now there a sign, like a tall pie sign on everything I type, even if I open up other documents all the words have a - line between them and that sign at the end of each sentence.....How do I get rid of that sign and open up a new document that is completely blank. I have tried to open a new doc and that sign pops up on everything. To toggle the display of nonprinting marks, press Ctrl+Shift+8. Alternatively, use the � button on the Home tab (Word 2007) or the Standard toolbar ...

using a date in vlookup
i want to perform a vlookup using the Now() function to generate the lookup value (A1), the 1st column in the table [col B] array will be all the dates in a year listed consequtive,, and the 2nd column being a value assigned to each day in the 1st column [B]. My formula is vlookup(A1,B1:C367,2). The result I get is "#N/A. What am i doing wrong? Thanks Tonso NOW() returns both the date and the time, so you would be better off using TODAY(), which only returns the date. Another problem might be that your dates in column B are really text values that just look like dates - see what happe...

How do you Export the MS word 2007 Document map to a .csv file
I want to export the headings in a word document to a .csv file either through the document map or the body of the main document. Is there a way that this can be accomplished. If not is there a way to selct all hading s and copy them to a text file? Thank you. B. McFarlin Someone may come along with a macro to select all the headings at once, but you can certainly select all the headings in a given style at once. In the Find dialog, use Format | Style to select the desired style, leaving the "Find what" box empty. Then click "Find in" and choose &quo...

vlookup excel and access...
assuming i have this code, is possible to use this vlookup withnthe adta into mdb access?... old scenario: Private Sub TextBox25_Change() Dim CODICE As Integer Select Case Me.TextBox25 Case "" Me.TextBox4 = "" Case 1 To 8 CODICE = Val(Me.TextBox25) Me.TextBox4 = Application.WorksheetFunction.VLookup _ (CODICE, Worksheets("TABELLA").Range("Q2:R9"), 2, False) Case Else Call MULTI_LINE_BOX End Select End Sub new scenario: Inested column Q and R in excel i have created a mdb into: \\my server\myserverdir\USER.MDB and into this mdb have inserte a table U...

Creating a word2000 document with access 2000 data
I have a database in access 2000 it is basically customer info. address and dates and timesof appointments. I need to place a button on the formpage that prints a letter using the customer details and the date & time entered in the database. Anyone have any clues as to the easiest way to do this.sort of done it using mailmerge but not what I want. Actually want a word document to pop up that the staff can just double check and press print. Any help would be appreciated even just a point in the right direction to a good source of information. to see if the following website's offer...

Vlookup within a vlookup
I am trying to lookup a cell within a table - but the table to use is found in another table. =VLOOKUP(B3,VLOOKUP(B2,F3:G9,2,2),2) Cell B2 is a dropdown box allowing one of the choices in colum f below. Column G represents which table to use for the initial lookup based on your choice in the drop down dox. column f column g Alt A 30 Yr fixed30 Alt A 15 Yr fixed15 All I get is an error - can someone help ? Thanks, Yosef It sounds like you would need to use INDIRECT within the lookup formula http://tinyurl.com/czxtt that thread shows the way to do it exce...

Software to convert English document to Hindi
Is there any software available to convert an English language text document to Hindi language document by using dictionary based translation (not transliteration). For example: English text: My name converted to : ???? ??? "S N" <sn@invalid.invalid> wrote in message news:##J8WYopKHA.3912@TK2MSFTNGP06.phx.gbl... > Is there any software available to convert an English language text > document > to Hindi language document by using dictionary based translation (not > transliteration). > For example: > > English text: My name > co...

Lost on Vlookup, match, etc....
Can someone walk me through this please? I a workbook that imports a years worth of data from filemaker to be analyzed and charted in excel. It contains several sheets, but I am concerned with worksheet 1 (daily data) and worksheet 2 (bodyweight). Daily data contains the raw data I pull in from Filemaker. It results in a table with a row for each day of the year. It has 12 columns, but in this instance, I am only interested in 2 of the colums Column F, (Date), and Column R (Bodyweight). There is only one entry per week for body weight. The bodyweight sheet has 3 columns (week #, date, and w...

VLOOKUP in VBA
On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...

Need TEMPLATE for Document Management
I am searching for a free Excel template or Access (2000) template for setting up and managing a corporate library. I looked on Microsoft Online but could not find anything that fit this particular need. I'm about to be bombarded with project documents/contracts/proposals/etc for Federal projects and need a way to inventory and find them if needed in the future. Any info would be appreciated. I scan old newsgroup postings for those that have only their initial inquiry, to determine why no one bothered to react to your question, issue, or concern. If you have not already...

Outlook Express and Word Documents received
I am currently in serious need of reciving MicrosoftWord documents via my email and for some reason outlook express is deleting all the attachments as they come in before I can look at them, how do I adjust the settings??? b Try posting this in an Outlook Express news group - this is not one of them. Outlook is a part of Microsoft Office and is what this group supports. Outlook Express is a part of Internet Explorer and has its own news groups. You can also find some good Outlook Express information here: http://insideoe.tomsterdam.com --� Milly Staples [MVP - Outlook] Post all replies...

VLOOKUP problem
I down a list of top 100 stock symbols to Excel and place them in Column A and their rank in Column B (1-100). the next week I download a new list of the top 100 stock symbols and their rank to Column C and D respectively. then I create column E using the function =VLOOKUP(C4,A:B,2,FALSE) Which tels me the rank each stock had last week and if a stock is new this week it puts "#N/A" in the appropriate cell in column E. How do I get the Vlookup function to put "NEW" in column E if the is new to the list and wasn't in the list last week? Ed =if(isn...

how to copy word document to excel
When I copy a Word document to a new Excel sheet, it adds rows between each line of the Word doc. Any ideas how to make this stop? Thanks! What are you pasting, exactly? Obviously, tabular data or actual tables would be suitable for Excel, while text paragraphs are more difficult to deal with. -- Stefan Blom Microsoft Word MVP "LM" <LM@discussions.microsoft.com> wrote in message news:45AAC4E4-29F1-40E2-BE30-C572B57E29A0@microsoft.com... > When I copy a Word document to a new Excel sheet, it adds rows between > each > line of the Word ...

VLOOKUP #40
Would like to post data from one worksheet to another. Could you provide a few more details about what exactly you want to do. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "dr" <drivera@opvista.com> wrote in message news:1f1001c52c08$ad4c33a0$a601280a@phx.gbl... > Would like to post data from one worksheet to another. ...

combobox and vlookups?
I am trying to add a combobox that when you select from the dropdown menu, the columns nextdoor automatically pull up corresponding data that is related to the selection from the dropdown list. Does this make sense? What do I do to set this whole thing up? (I don't know code). Thanks. -- Message posted via http://www.officekb.com You shouldn't need code for anything you described here. Set up the combobox with the list fill range, if any. Set the linked cell to the desired cell. Then, use the VLOOKUP command in another cell to look for the linked cell from the combo box to lo...

Can I import existing styles into a document?
I am working on a document to which I would like to apply just heading styles that I've created and use regularly, but leave everything else as it is. The document already has a heading 1 that is different from mine. Does this mean I have to modify the document's style or can I import my own and replace the document style? My colleague is also in the same situation, except that she has to use my styles. It would be useful if we didn't have to create the style over and over again, since we will be working on several documents. We are using Word 2003. Documents creat...

The document doesn't show any margins when I'm working on a doc
I can't see the margins as I'm working in the single page view of a document. I've checked to make sure that the settings are correct and they appear to be. The margins are set at 1 inch right and left, top and bottom. The ruler only shows the margins on the top not on the side. The top looks cropped and the view make the work surface look like a square instead of a normal rectangle. It's driving me crazy....please help. Assuming you're not working in Normal/Draft view, see http://sbarnhill.mvps.org/WordFAQs/WhiteSpace.htm -- Suzanne S. Barnhill Mic...

Can I stretch an Excel document vertically when printing?
I want to print a document in Excel. I have shrunk it to one page wide but now it is too flat. I want to stretch it so it fills the whole page. Is this possible cause I can't seem to find a setting? Maybe you should change the page layout to landscape and see what that looks like. Regards Trevor "jstratman33" <jstratman33@discussions.microsoft.com> wrote in message news:E23E8AF4-1D1D-4558-9FD7-5A8F556C785F@microsoft.com... >I want to print a document in Excel. I have shrunk it to one page wide but > now it is too flat. I want to stretch it so it fills the who...

Saving Excel documents from macros
Hi everyone To run a macro everytime I open an Excel document, I made a macro in a file named Personal.xls in ...\Program Files\Microsoft Office\Office10\XLStart. The macro runs well, but when I save the document it makes two files, one with the correct name (that I want) and another with a strange name like AE5B8100 or A06B8100 without extention. This files are also open by Excel when I make double-click on it. This files are only occupying space in disk and they aren't temporary files. Who knows why this happen? How can I resolve this problem? Thanks in adavnce. Best regards Rui Alva...

VLOOKUP
This is a multi-part message in MIME format. ------=_NextPart_000_0001_01CAC8EE.B1306170 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello, I use the VLOOKUP function to pull basic data from external data sheets, currently an example of my command looks like this: =VLOOKUP($A5,'[RT NP 67 MF.xls]ODD'!$A$1:$S$250,$L$1,FALSE) The "$AA%" is the data I am looking up and the "$L$1" is a variable to the column I am wishing to insert. The question I am trying to get an answer for concerns...

how to insert both page numbers and footers in the same document
Every time I try to insert both page numbers and a footer in the same document, one of them disappears. It seems to make no difference which I insert first You need to put the page number _in_ the footer (or header). What steps are you performing? Which version of Word? On Jan 19, 5:54=A0am, Sheila <She...@discussions.microsoft.com> wrote: > Every time I try to insert both page numbers and a footer in the same > document, one of them disappears. It seems to make no difference which I > insert first I'm guessing this is Word 2007 and you are using the Build...

incorporate paste value in vlookup
is there a way to incorporate "paste value" in a vlookup formula ? I have the vlookups in the column working and feeding off of the multiple worksheets. the data will be updated weekly (and new columns with the updated data added) and i would rather just copy new data on top of the old in the worksheets instean of having to add new worksheets with new data... as to keep the previous column from changing. -- problem ...

vlookup inside an if statement?
-------------------------------------------------------------------------------- Hi everybody! I have to do a vertical lookup of an object that might or might not exist, in case it doesnt exist normally it gives back Ref! but the thing is that if this objet doesnt appear on the data base is because is zero. This Ref! is not allowing me to make a sum since this objet is part of it. Maybe with an example would be easier to explain. i have to pull from a database the number of cars sold of three brands A,B, C, In the database unless is one car of the type it will not appear. lets say we hav...

Nested vlookup
My formula is: =((VLOOKUP($W$5181,Download!$A$1:$X$3403,3,FALSE))+(VLOOKUP($W$5182,Download!$A$1:$X$3403,3,FALSE)))*0.001 The formula looks up the value in "$W..."and returns the amount of orders. I alwayshave values for the first part of the lookup, but niot the second. THe issue is that if there nothing to report back for the second lookup, it gives me an "#NA" errror instead of at least returning the value for the first lookup. Any help? Thanks, TJ Wrap both Lookups in their own ISNA() wrapper to trap for that error:- =(IF(ISNA(VLookup1),0,VLookup1)+IF(ISNA(VL...