look up query

I have two worksheets. They are both identical with an exception of on column 
which is on my summary table. The tables are as follows:

data table: job_no; patient; hospital; fault; req_date; rec_date;

This table contains all the records from every job we have received.

summary table: job_no; patient; hospital; fault; req_date; rec_date; ACTION;

This table contains a sample of the data in the 'data table'. I add actions 
to each job on this table.

I need to know how to create a lookup so it will take the ACTION from the 
'summary table' and add it into a new column in the 'data table'. it should 
only add the action if there is an action and the jobs_no in both tables 
match as this field is unique.

any help would be appreciated
0
12/23/2005 8:59:01 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
452 Views

Similar Articles

[PageSpeed] 43

Hi Christopher,
Assuming the second worksheet is named Sheet2, enter the following into Cell 
H1 of the first worksheet (or H2 if you have a header row) and copy the 
formula down. If you have a header row, you need to change the A1's in the 
formula to A2's.

=IF(VLOOKUP(A1,Sheet2!$A:$H,8,FALSE)=0,"",VLOOKUP(A1,Sheet2!$A:$H,8,FALSE))

HTH
-- 
Ken Hudson


"Christopher Buxton" wrote:

> I have two worksheets. They are both identical with an exception of on column 
> which is on my summary table. The tables are as follows:
> 
> data table: job_no; patient; hospital; fault; req_date; rec_date;
> 
> This table contains all the records from every job we have received.
> 
> summary table: job_no; patient; hospital; fault; req_date; rec_date; ACTION;
> 
> This table contains a sample of the data in the 'data table'. I add actions 
> to each job on this table.
> 
> I need to know how to create a lookup so it will take the ACTION from the 
> 'summary table' and add it into a new column in the 'data table'. it should 
> only add the action if there is an action and the jobs_no in both tables 
> match as this field is unique.
> 
> any help would be appreciated
0
KenHudson (26)
12/23/2005 5:20:02 PM
Reply:

Similar Artilces:

All my icons look the same (except IE8 and the recycle bin)
i cant open any windows...when i click on them the open with window pops up and wants me to pick wat i want to open it with...i can only get on the internet in safe mode with networking. and when i try to open...for example paint it says invalid bitmap, or its format is not currently supported and other windows wont even open. got any ideas on how i can fix this?? On Sun, 30 May 2010 06:54:01 -0700, xxkoreanxx wrote in message <news:999F2CE9-8E21-47DC-8BA4-BAC1F1329F6E@microsoft.com>: > i cant open any windows...when i click on them the open with window pops up > a...

Looking for feedback
Hey everyone just started my own business doing consulting / training for Microsoft Outlook please check out my site www.outlookchallenged.com and provide any suggestions / feedback please. Any advice is good advice at this point. Thanks -- www.outlookchallenged.com Br Sense <outlookhelp@ftard.com> wrote: > Hey everyone just started my own business doing consulting / training > for Microsoft Outlook please check out my site > www.outlookchallenged.com and provide any suggestions / feedback > please. Any advice is good advice at this point. The best advice I have to offer ...

combinening two query results into one ?
There is a table which has [ACTIVITY_DESCRIPTION], [MATERIAL_PRICE], [LABOR_PRICE] fields. I created two queries- Query1 sorts [MATERIAL_PRICE] and Query2 sorts [LABOR_PRICE] in ascending order, according to a specific common criteria for [DESCRIPTION]. So, Query1 and Query2 produces same number of row for every query. I would like to combine query results into a single table, so i will have [MATERIAL_PRICE] and [LABOR_PRICE] in ascending order for a common criteria of [DESCRIPTION]. My target is to make a chart for values of material and labor prices in ascending order. Can I do ? hi, me...

Looking for CRM Solution
Does anyone use, or has anyone encountered, a CRM based solution used for residentail, multifamily property management? On Apr 28, 12:55=A0pm, MrB <M...@discussions.microsoft.com> wrote: > Does anyone use, or has anyone encountered, a CRM based solution used for > residentail, multifamily property management? The beauty of Microsoft CRM is that it can be customized for any business in any industry. It's ability to be customized using front- end tools (not within code) is , as far as I know, unparalleled by any other software package. You have the ability to create new entiti...

=COUNTA() with multiple look-up
Hi All, I would like to count the number of items that have a value "AAA" in column A and an amount >0 in column B. As I am not quite sure how to incorporate two look-ups in a counta formula I was wondering if anyone can give me the formula? Many thanks! Rgds, Robert Try something like this: =SUMPRODUCT((A1:A100="AAA")*(B1:B100>0)) or....if there may be some text cells in B1:B100 interspersed with the numbers =SUMPRODUCT((A1:A100="AAA")*ISNUMBER(B1:B100)*(B1:B100>0)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP &...

can I edit a picture to look faded?
I want to edit my picture to be faded so I can use it as background with text over it. "deborahm" <deborahm@discussions.microsoft.com> wrote in message news:4D93DD63-8553-447D-A0AC-44EE0E1BD9B3@microsoft.com... > I want to edit my picture to be faded so I can use it as background with > text > over it. In Publisher you can do it one of two ways. For both ways start by inserting the picture. Method 1, right click on picture and select Format Picture, click on picture tab, then click on the dropdown for Color under Image Control and select wash...

Variable Criterion for Query
I have a form, FORMA, that has a button and two text boxes. Clicking the button runs a doCmd OpenReport. The report contains a subreport which runs a query, which uses the values of the text boxes on the form for its criterion. The problem is that I now want to use the same subreport in another report with another form, FORMB. And of course at the moment it isn't working because the criteria of the query is pointing to a filed in FORMA, which isn't loaded. I can see various way around this but I'm wandering what is the slickest scaleable method. Here's m...

How to send a query result via Database Mail ?
There is a request to run a specific query and sent to end user. We are new to SQL Server 2005 and would like to seek your advice: 1) Is it a good idea to use SSIS package to run the script ? 2) Should we send the query result from 1) and sent to end user by Database Mail ? 3) Is there any reference so that we can learn how to set up the Database Mail (to send query result) ? Thanks Patrick 1) What scripts? 2) It depends on your business requirements, I have never used SSIS for sending db email 3) http://www.mssqltips.com/tip.asp?tip=1100 "Patrick" <P...

Connection b/t tables for query and form incorrect?
I have three tables whose data flow into a query: Table 1 - recipient info (recipients are autonumbered - primary key) Table 2 - personal services agreements (each psa is listed once, but there may be multiple p.s.a.s for each recipient - which is in a lookup field from Table 1) Table 3 - purchase orders (each psa may have multiple purchase orders; the psa field is a lookup here) A relationship is created b/t the tables for a query that brings in fields from all three. I think the relationship among the tables works fine b/c as long as there is a recipient in table 1 it shows up on...

data look up and return values across a row
I have a list in one spreadsheet, we'll call it 'spreadsheet A' that I need to cross reference with another larger spreadsheet, which we'll call 'spreadsheet B'. I'd like to remove everything from B that isn't on A. Is there an easy way of doing that? Chip Pearson has lots of info about working with duplicates at: http://www.cpearson.com/excel/duplicat.htm Betsey wrote: > > I have a list in one spreadsheet, we'll call it 'spreadsheet A' that I need > to cross reference with another larger spreadsheet, which we'll call > &#...

Looking for basic multiplaction formula
HI ALL, I'm trying to multiply cells d5-d9 by cells a5-a9 and put the answer in cells d14 - d18 ( using caps ) I have tried =(D5*A5) in d14 and then pulling it down to d18 PLEASE HELP ME IVE BEEN AT THIS FOR AN HOUR AND A HALF!! Sounds to me like you had it right... what's not working? In cell D14 enter "=D5*A5" Copy that and paste it in cells D15 to D18. Ken Puls, CMA - Microsoft MVP (Excel) www.excelguru.ca Concarp wrote: > HI ALL, > I'm trying to multiply cells d5-d9 by cells a5-a9 and > put the answer in cells d14 - d18 ( using caps ) > ...

Query by Form button
I am testing my Access app and I have a problem. The query by form button of my custom toolbar only works in the full version of access. In the runtime version it does not work at all with no message whatsoever. The other buttons works as expected in both environmentes. Query by form is the first of the toolbar. Does anyone know why or what can I do to trace the problem? Regards, This is by design in the runtime environment - the runtime doesn't have everything that the full version does. You need to create your own replacement for Filter By Form. www.allenbrowne.com.au has an exc...

Remove zero totals in query
I’m trying to find out how I can fix my query so that it doesn’t have records with zero values. Karl Dewey helped me with a clever way to create a query that pulls noncontiguous months for Labor_Cost totals. The criteria for the query is selected from an unbound form with two text boxes. This query works fine but then I needed to add some fields to it. I added fields, Category and DGroup and it was okay but then I added Product and that’s when the lines with zero totals showed up. It’s not for the same product. Here’s a visual example: Category DGroup Product Mo1 Mo2 Tr...

look up who is connected
hello i would like to check if any pc connects to my xp box which it shares a lot of dirs. can powershell do the job and how? not sure in PS but "netstat" could do this for you. regards Ramazan "ryan" <ryanlihk@hotmail.com> wrote in message news:eKobVuVrKHA.4220@TK2MSFTNGP05.phx.gbl... > hello > > i would like to check if any pc connects to my xp box which it shares a > lot of dirs. > > can powershell do the job and how? thanks On 2/15/2010 2:21 AM, RCan wrote: > not sure in PS but "netstat" could do this...

From Access Query to Excel and Open the File
Originally I posted this in the Access Forum and was told doing it this way will not work. Does anyone on this side know how to do this?? See Original Post below: Thanks, Hans I have imported the query into Excel and able to save it, but I have people that complained about having to find the file somewhere.... How would I run this query and NOT save to My Documents, but create a new Excel file and just open it, leaving the save option to the user? Also, can you do Excel cell formatting inside the VBA/Access code? DoCmd.OpenQuery "ISS Open Position Query", acViewNormal, acEdit ...

Union Query
A union query I developed has suddenly stopped working. The syntax is: SELECT * FROM [myQuery - Part 1] UNION SELECT * FROM [myQuery - Part 2]; Pulling up each query individually works fine. When i run the above union query I receive "Invalid use of Null". The number and order of the columns in each query are identical. Access 2003 here. Any ideas what could be the problem? I suspect it is the VBA code that you are using around the query, not the query, that stopped working. To confirm, does your query is executable from the query editor? If it does not, you also confirm t...

Looking for sheet to handle MPG, etc
HI I am looking for a sheet to handle gas mileage. things like MPG etc. It would be great to have a ready made sheet to handle this info thanks supermari -- Message posted from http://www.ExcelForum.com This shouldn't be too difficult unless you attended college at Texas A&M =miles driven/gallons used -- Don Guillett SalesAid Software donaldb@281.com "supermario >" <<supermario.1a3sk4@excelforum-nospam.com> wrote in message news:supermario.1a3sk4@excelforum-nospam.com... > HI > I am looking for a sheet to handle gas mileage. > things like MPG etc. &g...

Query Madness
Hello, For the Love of money i cant understand why my query wont Show my related information which is in another table. what im trying to do is from a particular table "tbl_ProjectAnalysis" Under the primary key field "ProjectQNo" is select a project, this field has a lookup query statement which looks up from table "tbl_Projects" Field "ProjectQNo and Field "ProjectTitle" but the bound column is "ProjectQNo" the 2 Tables are have a relationship as a 1 to 1 between the fields "ProjectQno" and "ProjectQno" I created ...

Look at this package from M$
--ivtelxoethnivzpa Content-Type: multipart/related; boundary="owjfpbnibkp"; type="multipart/alternative" --owjfpbnibkp Content-Type: multipart/alternative; boundary="rubabijkdwz" --rubabijkdwz Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Partner this is the latest version of security update, the "October 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express. Install now to help maintain the security of your computer from these vuln...

Look at these security update
--wpxhtpbmrmsmmz Content-Type: multipart/related; boundary="gpfmogvvgagrxl"; type="multipart/alternative" --gpfmogvvgagrxl Content-Type: multipart/alternative; boundary="uxubtxafyzmt" --uxubtxafyzmt Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Customer this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install ...

If statement to export query if record count is not null
Greetings, thank you very much to all who are reading this. Basically I have an automated email that goes out every Monday. Very simply it uses sendobject to email a query. I would like to add some logic that basically looks at the record count and if it isn't null...sends teh email as is...if the record count is empty I would like to have it send an email that basically says "The record set this week is empty." Below is the module converted from the original macro. I was having trouble modifying the module so any help would be greatly appreciated. Thanks in ...

Query which Value Returns Previous Record
I'm very new to access so please forgive me. I have a query that points to a table and returns a response based on a certain value. I was wondering if anyone could show me what to type in the query design view that if the value (example John Smith) is found report back not only that specific record but the previous record located above. Any help would be much appreciated. A thousand thanks Johnny Mac Hi Johnny, A mental picture that you should have for records in a table, within a JET ("Access") database is like fish in an aquarium. So, locating "the p...

CRM 4.0 Out Look Client
Hi All, I have a problem in CRM Outlook Client. After i do install Out Client and configure it to allow send and recieve emails through CRM, it is working fine. But due to some reasons the menu called "CRM" is disappearing from outlook Tool bar and it stops sending emails throgh CRM. It may happen after few days of working well. Could any body help me to find the reason for it. Any help would be greatly appreciated. ...

Update label and record query steps
Hi, I have a time consuming import code which has multiple steps. I amend the label caption to show which step the import is on but would like to show all steps and if possible place these steps in an archive table as the process is run by several users. At the moment I have: 1 - Preparing import...... which is replaced by 2 - Importing cust table 114,240 records (est. dur. 1 min)......... which is replaced by 3 - Importing sales table 842,600 records (est. dur. 4 mins)....... etc etc What I would like is: Query steps...... 18/02/2010 23:30:30 - Step 1 - Pre...

Oh look...the forum is not working again. NOT SURPRISES!!!!!
-- Cheers, Ryan Never stops working for me, but than I have the sense to use the UseNet groups, not a web forum. Bob "Ryan H" <RyanH@discussions.microsoft.com> wrote in message news:C709FADE-2DA0-4323-AAB3-3D3124831FDE@microsoft.com... > > -- > Cheers, > Ryan ...