lookup data in a different workbook

I export a list of items from Quickbooks. 
Quickbooks will export as a xls file.
I want to create a different worksheet or workbook that will look u
data in this file. I need to find data in a certain column in a certai
row. So when my data changes in Quickbooks, I export the file, and the
in another workbook or worksheet, update, based on the new or update
data. I have tried the lookup wizard but it will not let me use 
different worksheet or workbook. I can make it work in the sam
worksheet.
So what I'm looking for is a direction where to start. I have made man
complex Excel worksheets, using macros and formulas that I get fro
here. But I can't seem to figure out how to accomplish this one.

Thanks,
Davi

--
Message posted from http://www.ExcelForum.com

0
8/13/2004 4:10:17 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
418 Views

Similar Articles

[PageSpeed] 19

Try this:

assuming that you are looking up the value in C1 in a file called export.xls 
that is saved in your Data folder on your default drive.  Also assume that 
you have named the range that is being looked up as "codeslookup" and you 
want the formula to return the information in the 3rd column ...


VLOOKUP(C1,'\\Data\export.xls'!codeslookup,3)

Alternatively, if you have both files open, you can use the wizard - just 
click on the little "choose cell(s)" symbol to the right of the field in the 
wizard, then click on the other file and the appropriate range, then click 
again on the choose cell(s) symbol to be returned to the worksheet and cell 
you are working in.

I hope this helps you.

Bonnie

"dlaery >" wrote:

> I export a list of items from Quickbooks. 
> Quickbooks will export as a xls file.
> I want to create a different worksheet or workbook that will look up
> data in this file. I need to find data in a certain column in a certain
> row. So when my data changes in Quickbooks, I export the file, and then
> in another workbook or worksheet, update, based on the new or updated
> data. I have tried the lookup wizard but it will not let me use a
> different worksheet or workbook. I can make it work in the same
> worksheet.
> So what I'm looking for is a direction where to start. I have made many
> complex Excel worksheets, using macros and formulas that I get from
> here. But I can't seem to figure out how to accomplish this one.
> 
> Thanks,
> David
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 
> 
0
8/13/2004 5:15:03 PM
Reply:

Similar Artilces:

ComboBox Filter which pulls very specific Data in Excel into preset template
Hello, I must say that you guys are FANTASTIC at this board and i have read many of your posts throughout the years with great results. However, i am looking for a solution to an idea I have for a finance related project i am working on. I have a 4,000 row spread sheet with information that is summarized in the following fashion: Division Code, Branch No., Account Number, Account Name, Total Year to Date Expenses . Each Division encompasses a region (e.g. North, South, East, West) with many branches under it with corresponding expenses for each branch organized by different account n...

Table relationships and lookups
Hi guys, I may be a little over my head, I've had some experience in creating simple access db's. however this one will be extremely complicated as far as I can tell. Some backround info - i've got an excel spreadsheet currently that i would like to convert to Access. The spreadsheet does multiple lookups and calucations. This is for a Soccer club that i run to maintain roster information, dollars, scheduling and stats. I'm currently working on the scheduling pience. Here's what I have so far. tables. Club - Lists the teams in the club, home field name and ...

Invoice lookup by paid check number
I frequently have vendors call me asking for information about what invoices were paid by a check number. Is ther an easy way to look this information up? -- Rodger You could go to Inquiry>Purchasing>Transactions by Document, put your check number in the 'from' and 'to' fields. Once the document is displayed in the scrolling window zoom back on the 'Unapplied Amount' field. Viola! the documents paid by the selected payment are listed. Unfortunately there isn't a print icon on this inquiry window, but I think it's the information you wanted. &quo...

filtering data into different workheet #2
Thanks for the help but i managed to find my way using pivot tables. Thanks again Swmasso -- swmasso ----------------------------------------------------------------------- swmasson's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=838 View this thread: http://www.excelforum.com/showthread.php?threadid=27045 ...

Hide data series in chart
Excel 2002 SP3 Win XP HE *Follow-up to: microsoft.public.excel.charting* Hi, I have a chart with 3 series. 2 are on right axis (secondary) and 1 on left (primary). Is it possible for me to toggle on or off 1 of the series? I cannot just hide it by choosing "none" as plot data as it still plots it even if not visible. I am really trying to fix scaling problems.... Thanks. One of the examples in the page below shows how to show and hide chart series using checkboxes: http://peltiertech.com/Excel/Charts/ChartByControl.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorial...

question related to Data Vaildation
Dear all, I have two columns. Each column contains a defined value and each value carries some predefined details. I am trying to place a dropdown through data validation, so that i select any value, i should be getting all the details related to that value without any extended space. Like below-: A operations downtime client figure B ratings flow chart technical support If i select A, I want to get the details of A as dropdown, i select B, i want to get the details of B. Kindly help ! Refer the below by Debra Dalgleish ..(you will need to fill the blank cells ...

excel extract data from one sheet to another
a b c Name Vault Rank Blake, Jasmine 7.900 1 Fattouche, Chloe 7.600 3 Fattouche, Nicole 7.700 2 Pratt, Erika 6.100 5 Salem, Alexandra 6.300 4 Mills, Sara 7.700 2 Ranked compares each via =SUMPRODUCT(--(C5<$C$5:$C$148),1/COUNTIF($C$5:$C$148,$C$5:$C$148&""))+1 This data is on one sheet and already calculated the rank. Need to pull the top three and put on seprate work sheet. Including ties. Name Vault Rank Blake, Jasmine 7.900 1 Fattouche, Nicole 7.700 2 Mills,Sara ...

Link Excel cell data to Powerpoint text boxes
Hi there I am using Excel 2002. On cell A1, I have text "Apple", On cell A2, I have text "Banana". I also have a powerpoint file with two text boxes What i wanna do: 1) To create a link between this Excel file and this Powerpoint files. So, that, "Apple" will appear in one text box in Powerpoint, and "Banana" will appear in another text box. 2) Also, whatever i change or update the Excel cell e.g. "Apple", it will be changed in Powerpoint automatically. Any tips? Thanks a lot. cheers:) Jon Peltier a Excel MVP has a macro that will d...

Access 2007 two different versions of same database
I opened up the backup copy by mistake, and made some changes. When I realized what I did, I made a copy of the backup. Can I synchronize the original and "backup" copy, and also will I be able to manually accept/reject changes? (I won't do that again!) Thanks very much for any help you can give me. Kim2, MAKE A BACK-UP FIRST! You can't synchronize in the way that you are thinking. You can copy all the forms, reports, queries, etc... into your *current* database. They will come in with a '1' after them and just remove the '1' OR delet...

Consolidating data from 40 worksheets
I have a bunch of worksheet templates (about 40) sent out for people t fill out. I am looking to consolidate the data in one master exce worksheet. What is the best way to accomplish this? I have trie linking sheets, but within the master sheet worksheet, I am usin =SUM() and the formula just gets too long. Any suggestions would b greatly appreciated. Thank you -- rhelle ----------------------------------------------------------------------- rheller's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2408 View this thread: http://www.excelforum.com/showthread.php?th...

Publisher cannot open files from a different version.
Hi Folks, I hope you can help me with this: I changed hard drives and re-installed Publisher 2000. This is the SAME version I was using previously, loaded from the SAME cd-rom. However, when I try to open any of my Publisher documents I get the message: "Publisher cannot open files from a different version." Can anyone tell me what I am doing wrong? I even went to the update section of Microsoft and downloaded all the updates. Still I get the error message. Anything anyone can do to help will be greatly apreciated as I have quite a few files that I would hate to lose. Th...

Data conversion Cam3
Has converted from Cam3 to RMS? Cam3 uses Btrieve 6.15. I have a prospect who is looking at switching to RMS from CAM. They want Inventory, Customer history and purchasing history. Any suggestions woulld be appreciated. Pat See if the program will export to a comma type file. The it is easy. "pat@arms" <patarms@discussions.microsoft.com> wrote in message news:9073BC10-884B-48F7-AEDB-AF58093F65CA@microsoft.com... > Has converted from Cam3 to RMS? Cam3 uses Btrieve 6.15. I have a > prospect who is looking at switching to RMS from CAM. > They want Inventory, Custo...

Catagorize Column Data by Name
I have a portfolio spreadsheet with approx 100-200 rows where each row depicts a unique project and each column depicts a calendar week. The cells in each row are color coordinated to illustrate the phases of the project. (For instance, I could have 3 consecutive tan cells illustrating analysis, 5 consecutive "no fill" cells depicting development, and 4 gray cells indicating testing. It is actually a bit more colorful but I will keep it simple for now!) In each cell there is a numeric value describing how many projected resource hours are to be applied to that phase for th...

Message Alert for Changing cell data
I run a daily report in Excel that tracks levels of products in tanks It is important to be able highlight tanks that do not change levels o any given day. I insert the date in the report automatically. Iwoul like to know how to flash an alert when any selected tank level does not change for that specific da -- jsmith5 ----------------------------------------------------------------------- jsmith59's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=786 View this thread: http://www.excelforum.com/showthread.php?threadid=32051 How about conditional formatting to c...

Lookup
Q103 Q102 Q202 Q302 Q402 Q103 Q203 How can I lookup the Q103 in the row above and then have it pull the number to the right one cell (Q203)? thanks If I understand correctly =INDEX(A2:F2,MATCH(A1,A2:F2,0)+1) where q103 is in a1 and q102-q203 is in a2-f2 Lance >-----Original Message----- >Q103 > > >Q102 Q202 Q302 Q402 Q103 Q203 > > >How can I lookup the Q103 in the row above and then have >it pull the number to the right one cell (Q203)? thanks >. > matt wrote: > Q103 > > > Q102 Q202 Q302 Q402 Q...

Help with LOOKUP function
This function is in a workbook with 2 sheets. It _almost_ works perfectly. These "C" columns in two different sheets '2005-2006'!C:C,'2004-2005'!C:C, contain names of people. The D column in one of the sheets - '2004-2005'!D:D - contains a date associated with the person's name from the C column of 2004-2005 sheet. This formula is in the "D" column of Sheet 2005-2006. =LOOKUP('2005-2006'!C:C,'2004-2005'!C:C,'2004-2005'!D:D) The concept is for the formula to lookup the value (person's name) in column C of 2005-2006 a...

Charting data against dates where dates are not at fixed intervals
I have several data points that are the result of a pivot-table and I'm trying to plot them in a pivot-chart (I can easily get them out of the pivot table if the graph format is not supported for pivot-charts). Here's an example of the data: 2005/01/15 2005/02/22 2005/03/18 2005/05/20 X 16 18 22 12 Y 4 6 3 2 Z 14 19 12 34 What I'd like to do is plot the data (preferably on a line chart) such that ...

A Lookup function does not work
Hi, This is my first posting. I am using Exel 2000. I have 2 separate spreadsheets that have some similar columns but not all of the data in the similar columns is the same. What I want to do is take column A in spreadsheet#1 and find this same value in Column B in Spreadsheet#2 and then insert into column 3 in spreadsheet #1 a value from a different column in spreasheet #2 that corresponds to the row in which the value was looked up in Column B in spreadsheet#2. What I am doing is comparing 2 different inventory files that have stock codes in columns and quantities in another column, but n...

return cell reference in a table based upon given lookup criteria
Is there a way to return the cell reference, or column/row coordinates, of a cell within an array or table by providing lookup criteria? Perhaps something like this: For a table of value in A1:E10 F1: (the value to find) G1: =ADDRESS(MAX((A1:E10=F1)*ROW(A1:E10)),MAX((A1:E10=F1)*COLUMN(A1:E10))) Note: Commit that array formula by holding down the [Ctrl][Shift] keys and press [Enter]. That formula returns the address of the 1st cell containing the value in F1, or #VALUE! if there is no match. Am I on the right track here? *********** Regards, Ron XL2002, WinXP-Pro "Travis" ...

Repost- account list balance and ending balance different amounts (in MONEY 2004
When I click Account list my bank balance shows an amount say "A" dollars.. but when i go to the particular account the ending balance is not "A" dollars but "B" dollars. Why is there a discrepency?? Also the cash flow takes the "A" dollars into account while computing instead of the current ending balance "B". The difference is "B-A=C dollars" Could it be because i cancelled a electronic payment of "C" dollars and marked it void in the account and then deleted it later thanks nithya ...

Advanced Lookups
Is there any way to make an advanced lookup the default lookup? so you don't have to always choose that option when doing a lookup? Thanks for any help. Tracey D Advanced lookups ARE the default unless you've done something to make it now so. There isn't any way to "choose" the option when doing a lookup that I know of unless you have some type of customization (easy to do) that would give the user an option. patrick dev support -- This posting is provided "AS IS" with no warranties, and confers no rights. "Tracey D" <...

transposition of data
Hi, I have some data in the column A of a sheet and in another sheet of the same file, I need to use the same data but horizontaly this time : I need that A2 in first sheet come in B1, A3 in C1, A4 in D1... And I don't want just the data, I need to this to update itself his the first data change. I have try to do in column a of the ne sheet A1 =A1, A2=A2... and the n copy it to do a pastespecial formula-transpose, but it doesn't work... Thanks for any hel Happy Christma Benjamin "Ben.c" <anonymous@discussions.microsoft.com> wrote in message news:5796715A-9C7D-4BB5-A29E...

Can I paste data skipping the hidden cells ?
Can I paste data missing out cells that are hidden ? Excel's paste does not skip hidden rows/columns. -- Jim "tclark67" <tclark67@discussions.microsoft.com> wrote in message news:670D4A6F-1C57-4A11-AAB6-1190D2461EB3@microsoft.com... | Can I paste data missing out cells that are hidden ? Yes, you can ! ! ! Try activating the autofilter in the desired column, and selecting the (NonBlanks) Then Select the entire column, Copy, and paste where you like, thats all ! ! Saludos NlCO -- NlCO ------------------------------------------------------------------------ Nl...

trace order of data entry
I have 2 columns. in column A I will enter a name. in column b I will enter a number from 1 to 10. The names are not entered in the order 1 to 10 but randomly. Example I can enter number 5 then 2 then 9 then 1 and so on. How can I track the order in whick I names are entered. Can I do this on a different column rather than tracking changes. -- Radha Copy the code below and put it into the worksheet's code module. To do that, right-click on the worksheet's name tab and choose [View Code] from the popup list that appears. Then copy the code below and paste it into the module. ...

how to create chart with five data variables
I need to create a chart off of a spreadsheet with five variables I have to show: Room Fiscal Year (2003, 2004, 2005, 2006) Business Hour usage After-Hours usage Quantity (number of times the room was used that year) Currently I have the data as Fiscal Year in rows by Business or After Hours (FY03 Business Hours, FY03 After Hours, FY04 BH, FY04AH, etc.) Rooms in columns, and Quantity as cell value. What I want to do is create a stacked column chart that shows has Room for the X-axis value, Quanity for the Y-axis, Fiscal Year on the Z-axis (so it would be a 3-dimensional chart) and stack the ...