lookup tables across two workbooks

Hi
I have two workbooks to which i wish to do a lookup for.

The first wookbook contains totals via subtotals as below

A1  0741.702.113    B1 $250.00       C1 EXPENSES

A5  0745.753.112    B5 $2510.00      C5 BANK FEES

The other workbook contains a vlookup to get the dollar amount from
the first workbook  and inserts into the second workbook.(column 2)

It appears it doesn't work because the first workbook is total made up
of subtotals with the other rows being hidden.

Do i have to use the vlookup combined with match etc?? or maybe the
format is the problem??

Thankyou  in advance

Darrell
0
darrellps (3)
9/28/2004 3:23:06 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
696 Views

Similar Articles

[PageSpeed] 7

The hidden rows didn't affect my =vlookup().

If you're positive that there is a match, pick out the two cells that that
should match up and put something like this in an unused cell:

=a2=sheet2!a9

If it comes out True, then the values matched.  If it evaluates as False, then
maybe you don't have what you expect in both cells.  (extra
leading/trailing/embedded spaces???--numbers versus text (123 <> '123).)

darrellps wrote:
> 
> Hi
> I have two workbooks to which i wish to do a lookup for.
> 
> The first wookbook contains totals via subtotals as below
> 
> A1  0741.702.113    B1 $250.00       C1 EXPENSES
> 
> A5  0745.753.112    B5 $2510.00      C5 BANK FEES
> 
> The other workbook contains a vlookup to get the dollar amount from
> the first workbook  and inserts into the second workbook.(column 2)
> 
> It appears it doesn't work because the first workbook is total made up
> of subtotals with the other rows being hidden.
> 
> Do i have to use the vlookup combined with match etc?? or maybe the
> format is the problem??
> 
> Thankyou  in advance
> 
> Darrell

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
9/28/2004 8:20:14 PM
Or maybe the Vlookup formula needs FALSE as its fourth argument?

Dave Peterson wrote:
> The hidden rows didn't affect my =vlookup().
> 
> If you're positive that there is a match, pick out the two cells that that
> should match up and put something like this in an unused cell:
> 
> =a2=sheet2!a9
> 
> If it comes out True, then the values matched.  If it evaluates as False, then
> maybe you don't have what you expect in both cells.  (extra
> leading/trailing/embedded spaces???--numbers versus text (123 <> '123).)
> 
> darrellps wrote:
> 
>>Hi
>>I have two workbooks to which i wish to do a lookup for.
>>
>>The first wookbook contains totals via subtotals as below
>>
>>A1  0741.702.113    B1 $250.00       C1 EXPENSES
>>
>>A5  0745.753.112    B5 $2510.00      C5 BANK FEES
>>
>>The other workbook contains a vlookup to get the dollar amount from
>>the first workbook  and inserts into the second workbook.(column 2)
>>
>>It appears it doesn't work because the first workbook is total made up
>>of subtotals with the other rows being hidden.
>>
>>Do i have to use the vlookup combined with match etc?? or maybe the
>>format is the problem??
>>
>>Thankyou  in advance
>>
>>Darrell
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
9/28/2004 9:03:40 PM
And if none of these suggestions worked, maybe the OP could paste the formula
that didn't work into the followup message.


Debra Dalgleish wrote:
> 
> Or maybe the Vlookup formula needs FALSE as its fourth argument?
> 
> Dave Peterson wrote:
> > The hidden rows didn't affect my =vlookup().
> >
> > If you're positive that there is a match, pick out the two cells that that
> > should match up and put something like this in an unused cell:
> >
> > =a2=sheet2!a9
> >
> > If it comes out True, then the values matched.  If it evaluates as False, then
> > maybe you don't have what you expect in both cells.  (extra
> > leading/trailing/embedded spaces???--numbers versus text (123 <> '123).)
> >
> > darrellps wrote:
> >
> >>Hi
> >>I have two workbooks to which i wish to do a lookup for.
> >>
> >>The first wookbook contains totals via subtotals as below
> >>
> >>A1  0741.702.113    B1 $250.00       C1 EXPENSES
> >>
> >>A5  0745.753.112    B5 $2510.00      C5 BANK FEES
> >>
> >>The other workbook contains a vlookup to get the dollar amount from
> >>the first workbook  and inserts into the second workbook.(column 2)
> >>
> >>It appears it doesn't work because the first workbook is total made up
> >>of subtotals with the other rows being hidden.
> >>
> >>Do i have to use the vlookup combined with match etc?? or maybe the
> >>format is the problem??
> >>
> >>Thankyou  in advance
> >>
> >>Darrell
> >
> >
> 
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
9/28/2004 9:06:43 PM
If you are refering to external links to other workbooks, the othe
workbook must be open for the formula to work. I'm assuming you
formula looks something like this:

=vlookup(a1,[workbook1]Sheet1!$a:$b,2,0)

That formula will work fine when the workbook1 is open. When workbook
is closed you may notice the formula change to something like this.

=vlookup(a1,'C:\mypath\[workbook1]Sheet1'!$a:$b,2,0)

If your formula resembles the second one, you may not have workbook
open, or you have chosen 'no' to update links to other workbooks.

One more way to check if your formula's are experiencing this is b
going to Edit>Links.

Hope that helps..

Ben Sullin

--
bsullin
-----------------------------------------------------------------------
bsullins's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=139
View this thread: http://www.excelforum.com/showthread.php?threadid=26408

0
9/28/2004 9:19:25 PM
This type of =vlookup() formula has always worked for me with closed workbooks.

There are other functions that don't work so nicely, though (=indirect(),
=sumif() spring to mind).

bsullins wrote:
> 
> If you are refering to external links to other workbooks, the other
> workbook must be open for the formula to work. I'm assuming your
> formula looks something like this:
> 
> =vlookup(a1,[workbook1]Sheet1!$a:$b,2,0)
> 
> That formula will work fine when the workbook1 is open. When workbook1
> is closed you may notice the formula change to something like this.
> 
> =vlookup(a1,'C:\mypath\[workbook1]Sheet1'!$a:$b,2,0)
> 
> If your formula resembles the second one, you may not have workbook1
> open, or you have chosen 'no' to update links to other workbooks.
> 
> One more way to check if your formula's are experiencing this is by
> going to Edit>Links.
> 
> Hope that helps..
> 
> Ben Sullins
> 
> --
> bsullins
> ------------------------------------------------------------------------
> bsullins's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1394
> View this thread: http://www.excelforum.com/showthread.php?threadid=264088

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
9/28/2004 9:45:25 PM
Reply:

Similar Artilces:

How do I compare data in two worksheets to find matching cells?
Each day I work with two worksheets. One is a list of numbers that is given to me by someone else. The other list is something I make myself, again it is a list of numbers. I need to know if there is a way I can compare the data to find out if any of the numbers I input on my list match up to the ones on the list I am given. I am aware of the "Find" function, but I'm working with lists of hundreds of numbers, and it's really annoying to take each one and "find" it on another worksheet. Any answers? Hi Gary, See Chip Pearson's Tagging Duplicates page. In ...

How do I create a one variable data table? #2
...

shared workbook issue
Hello All - I am using Excel 2007 and have a shared workbook that is also password protected. One user of the workbook attempted to save changes and received an error message that reads: "Caution: You are attempting to save a shared workbook that is also protected with a password. Portions of the file, including the change history, will not be encrypted. To help ensure the security of your file, unshare the workbook or delete the change history from the document. Do you want to save this file?" I would like to keep this as a shared workbook, so I attempted to delete t...

Pivot table formats
I have created a pivot table that has three fields; Date, raingauge and rainfall. I want to export the output of this table into an Access database. However the table produced only lists date as a single entry instead of listing the date for each raingauge. Therefore I have to manually fill in the missing date data to export it to Access. Is there any way of formating the date field so that it is listed all the way down the table thank Alan What you can do is copy the pivot table and paste special > value over itself. Select the entire range with the gaps, press F5 > Special > Bl...

Filter custom lookup field according to another field on form
Customising the form for a "Case". In the "Overview" section of the "General" tab there is a "Customer" field which corresponds to the "Account" or "Customer" (company, if you like) associated with the Case. I have added a "Contact" attribute as a Lookup which will associate the "Case" with a particular "Contact" belonging to the "Customer"/"Account" (by creating a relationship in the "Contact" entity, which is then exposed as an attribute in the "Case" form). How...

blank two fold 8 1/2 x 14 brochure templates
where can I find a download for a tw 0r three fold blank 8 1/2 x 14 brochure template It is a simple page setup. Setup your printer for legal stock and then your page. In the arrange menu select two or three columns. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "kawika" <kawika@discussions.microsoft.com> wrote in message news:E99D8D0A-E6C8-48A2-A5AF-C5B6E365219C@microsoft.com... > where can I find a download for a tw 0r three fold blank 8 1/2 x 14 brochure > template ...

Pivot table calculated field: multiply Sum of FieldA with Max of FieldB
Hello usenetters, My question concerns Excel 2003. A user asked me about a calculated field in a pivot table. Some of the fields are DT_NOW, GW_WCR, GW_LOB. The formula of the calculated field should depend on the date (DT_NOW): Up to 30/06/2009: Rotation = GW_WCR / GW_LOB * 91 Starting 01/07/2009: Rotation = GW_WCR / GW_LOB * 28 She added a field to the underlying data called ROT_FACTOR, filled with 91 until 30th june and filled with 28 starting 1st july. The current formula is =IF(ROT_FACTOR>300000;GW_WCR/GW_LOB*91;GW_WCR/GW_LOB*28) The cutoff value 300000 is an arbitrary value base...

link sheets in workbook
I have 6 worksheets in a workbook. I want to add values to the cells in the first worksheet, but then these values will automatically added or updated in other 5 worksheets that have the same cell numbers. How do I do this? Thanks Hi on your other sheet enter ='sheet1'!A1 -- Regards Frank Kabel Frankfurt, Germany "Raymond" <NotValidEmail@yahoo.com> schrieb im Newsbeitrag news:CNKdnUXLI4OmvxzcRVn-gQ@comcast.com... > I have 6 worksheets in a workbook. I want to add values to the cells in the > first worksheet, but then these values will automatically add...

Kits and Kit Components and SQL Tables
In the past few weeks I have been working on pulling data into an Excel Workbook from various GP Dynamics tables. The purpose of this workbook is to compile data to be used in a cash flow analysis. I have posted several questions on here and responders have been very helpful in answering my questions. I have a new one that hopefully someone can answer... I am pulling in sales line item data from tables SOP30300, SOP030200 as well as IV00101 (for item type). I am using IV00101 because some of our sales include items set up with an item type of "service" so the costs that ar...

Combining two companies into one
We are approaching a point where management wants to combine two separately operating companies into one entity. How do I merge the one into the larger? Can I do this within Great Plains and migrate the tables & information? There isn't a conflict with cost center-acct#'s on the income statement but there would be on the balance sheet. Vendor names & numbers would need to be redone as well. Management will want to be able to combine by the end of the year. I know I could just create new cost centers and quit using the old company but if there is any way to combi...

Adding a calculated ROW to a pivot table
Does anybody know how to add a calculated ROW to a pivot table? I have a pivot table that is returning totals at the bottom, as it should, but I *also* need it to return that total as a percentage of grand total, directly beneath the total. I've always done this free-form in the cells below a pivot table before, but the size of this pivot is dynamic so that's not an option. Also--I'm using the pivot in Access, not Excel directly. Anybody have any tips? Thanks! ...

setting up tax tables in RMS
I have a client who has special tax needs. $0-$1600 is taxed at 9.25 $1601-$3200 is taxed at 9.75 $3201- and above is taxed at 7% so for example- for a $3,300.00 item- the tax would be $311.00. How would I set this up in RMS? Help! Thanks Andrea Andrea, Create 3 Sales Taxes. Sales Tax 1 Minimum Taxable Amount = None Maximum Taxable Amount = $1,600.99 Tax Rate = %9.25 Sales Tax 2 Minimum Taxable Amount = $1,601.00 Maximum Taxable Amount = $3,200.99 Tax Rate = %9.75 Sales Tax 3 Minimum Taxable Amount = $3,201.00 Maximum Taxable Amount = None Tax Rate = %7 Assign all of these taxes to one...

Need to remove data in table due to input mask; cannot close.
ACCESS Table open, unable to close or modify telephone field. Had tried i/p mask of 0 due to "Unlisted" numbers appearing right-justified when doing a report. I sure thought I changed it back to a NO MASK prior saving the table. Now, table is open and I am unable to do anything with that field, even deleting the content, to satisfy the mask problem. Hands seems to be tied in 'catch 22'; I would have thought (!!??) that the table would not have saved properly if the input mask did not match the field contents. I'm stuck! Thank you so much. =?Utf-8?B?b2ZmZXJvY2t...

Sql Server Indexing With Two or More Columns
I got a question with indexing. If I create an index and select 2 or more columns, what is the difference with that and creating 2 (or more separate ) indexes for them? Thanks mark It depends on what you are doing If you have WHERE Last=@p1 AND First=@p2 there no need to have two indexes , however having WHERE First=@p2 the first index on Last,First may or may not be useful, so in that case having two NCI may be a good idea I mean you need testing it, and make a decision.Also there is no need to create NCI on every column, it is especially true in SQL Server 2005 and onwards w...

How to change existing table record value by VBA and How to add new record in existing table by VBA
Hi all, I got table in my database with name "tbldata" and i have two fields in that table with the name "Ticket_No" and "Amount". In "Ticket_No" filed column i have value "SD001" and in same row of "Amount" column i have figure "50". With VBA how can i lookup for value "SD001" in "Ticket_No" field and change figure "50" to "30" which is appearing in same row of "Amount" column field. My other question is that how can i add new record in same table with VBA. I want...

How to create temp table ?
Can I create temp table by myself for report writer ? Thx ! Sorry, the answer is no. You need Dexterity to create a temporary table and a Dexterity script to read other tables and populate the temp table's contents. You may be able to use VBA with ADO to obtain the data you need for your report. David Musgrave Senior Development Consultant MBS Services - Asia Pacific Microsoft Business Solutions http://www.microsoft.com/BusinessSolutions mailto:dmusgrav@nospam-microsoft.com Any views contained within are my personal views and not necessarily Microsoft Business Solutions polic...

Can you manually delete data from the Account and Contact tables?
I am doing some testing and am presently deleting data from the Accountbase and Contactbase tables in the MSCRM database. I know that the supported method is to backup and restore the databases but ... Anyone have experience with this? I am only loading data from the CDF database to these 2 tables. In order for me to delete the data I have to temporarily disable "Enforce Integrity" in the 4 relationship between Accountbase and Contactbase. It works ... but will I have problems in the future after I have finally loaded all my good data? Danny Danny, There could be problems introd...

Presenting all months in a year in a Pivot Table/Chart when all months are not included in source data
Excel 2007, Windows 7 I'm creating a series of pivot tables and charts to show price data by month in a given year for different categories of products (in different locations). If I have entries for most but not all months in a given year, is it possible to have excel render a table and chart that displays all 12 months of the calendar year even though not all are included in the source data? As it works by default, it only presents data for those months that are included in the source data. I know a work around would be to include the missing months with a blank entry in the source d...

Best Practices for Development in GP's using Custom Tables
Is there any such document/direction? We write custom solutions for our clients using everything from eConnect, to ..NET etc. Is there a guideline for creating or not creating, custom database tables in GP's productions databases? Cheers, JM ...

pivot table help #2
I am extremely new to pivot tables and trying to just get ave, max, and min of a couple of columns ... I keep getting #DIV/0! errors and don't know what the problem is. any ideas anyone??? -- evg1 ------------------------------------------------------------------------ evg1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35043 View this thread: http://www.excelforum.com/showthread.php?threadid=547887 Do you have errors in any of those fields in the raw data? Do you have any numbers in the field that you're averaging? evg1 wrote: > > I am ...

Pivot Tables #17
Monthly dump info from Access into Excel and then do a pivot table. I am able to refresh the data in the pivot table from the raw data fine, but I can't group on the Date field. The raw data has each day in the month and I group it into a month. When I try to group the Date field I get the error "Cannot group that selection." Any help would be appreciated. Thanks The dates have to be numeric, that way you will get the option of grouping by month, week etc. If they are dates and look numeric as in 12/15/03 or similar depending on the region, select an empty cell, copy...

Formulas Referencing Pivot Table results
I'm using a pivot table to sum batting statistics. I calculate the batting average by referencing cells in the table. However, when I add a new batter or remove a batter, and refresh the table, the formula references are incorrect. Why is that and is there a way to maintain formula references as new batters are added/removed? Thanks John It sounds like you could use a calculated field in your pivottable. show the pivottable toolbar if it's not already shown. click on PivotTable dropdown click on formulas click on calculated field Give it a nice name (Batting Average?). an...

Advanced Lookups with a secondard sort
Hi, When I put in an Advanced Lookup for a Segment (Entity Code - 0WHIIN), it looks like the account numbers are sorting by Entity and then DEX ROW ID and not by account number. The sort should be by Entity (Segment 2 - Entity and then by Main Account Number - 00010, not Dex Row ID. This is a sample of the sort This is the Dex row ID for the accounts 00010-0WHIIN-00-00 1 00200-0WHIIN-00-00 2 00250-0WHIIN-00-00 3 00260-0WHIIN-00-00 ...

two exchange servers, one calendar view?
Is there any way to combine the information from two different Exchange calendars(from 2 different servers) into one view? Are there any add ons that can accomplish this? We have some employees that are working with another company on a long term project, and the two scedules are conflicting. I told her to just use one as a primary inbox that people must realize is your default for scheduling appointments, but she I'm sure some will slip through the carcks and create larger headaches down the road. On Tue, 6 Sep 2005 09:11:05 -0700, Dan <Dan@discussions.microsoft.com> wrote: ...

How to use two parameters in input of popup Variables
HI All Gary's Student kindly made some to code to allow input of variables via popup : Dim colstring As String Dim findit As Double, replacewith As Double colstring = Application.InputBox("Select Column ....", "Stage 1 of 3", Type:=2) findit = Application.InputBox("Column Selected : " & colstring & vbCr & vbCr & "Select Value To Replace?", "Stage 2 of 3", Type:=1) replacewith = Application.InputBox("Column Selected : " & colstring & vbCr & vbCr & "Value Selected : " &am...