Count, Sum, Index,Match or other formula needed

Using Excel 2003:
One row is a set of numbers.  I need to match that to another worksheet and 
find certain results in it.  Here's the trick.  This second worksheet will 
have these numbers over and over again with either Yes or No in the column I 
want returned.  Example, number in cell one is shown 10 times in the second 
worksheet sometimes with yes and sometimes with no in the column I want back. 
 

The result need to find the number in the 1st worksheet and return a No if 
any of the matching in the 2nd worksheet contain a No in the column I want 
returned even if sometimes it has a Yes for this number.

Example:
Worksheet 2:
#                                Yes/No
123                              Yes
456                              No
789                              No
789                             No
789                              Yes
123                              No
456                             No
456                              No
123                               Yes

Results should be:
123                               Yes
456                               No
789                               Yes

Thanks.
0
Utf
5/4/2010 6:22:10 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
847 Views

Similar Articles

[PageSpeed] 39

Not elegant, but this should work:
=IF(SUMPRODUCT(--(Sheet2!$A$1:$A$200=Sheet1!A1),--(Sheet2!$B$1:$B$200="No"))>0,"No",IF(SUMPRODUCT(--(Sheet2!$A$1:$A$200=Sheet1!A1),--(Sheet2!$B$1:$B$200="Yes"))>0,"Yes","Not found"))

Adjust ranges as needed. In Excel 2003 you can't use whole columns with 
SUMPRODUCT.

BTW, your sample data has at least one NO for each number, so the results 
should be No, No, and No.

Hope this helps,

Hutch

"Nadine" wrote:

> Using Excel 2003:
> One row is a set of numbers.  I need to match that to another worksheet and 
> find certain results in it.  Here's the trick.  This second worksheet will 
> have these numbers over and over again with either Yes or No in the column I 
> want returned.  Example, number in cell one is shown 10 times in the second 
> worksheet sometimes with yes and sometimes with no in the column I want back. 
>  
> 
> The result need to find the number in the 1st worksheet and return a No if 
> any of the matching in the 2nd worksheet contain a No in the column I want 
> returned even if sometimes it has a Yes for this number.
> 
> Example:
> Worksheet 2:
> #                                Yes/No
> 123                              Yes
> 456                              No
> 789                              No
> 789                             No
> 789                              Yes
> 123                              No
> 456                             No
> 456                              No
> 123                               Yes
> 
> Results should be:
> 123                               Yes
> 456                               No
> 789                               Yes
> 
> Thanks.
0
Utf
5/4/2010 8:01:01 PM
Reply:

Similar Artilces:

Click in cell w/ formula and get colors in referenced cells
Hi, When you click in a cell with a formula, Excel will then put color around the cells that are referenced. Somehow I turned that feature off. Now I do not get colors in th other cells. Where is that option to turn it back on???? I've looke and looked but I just can't find that option. Thanks for the help -- albea ----------------------------------------------------------------------- albean's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2875 View this thread: http://www.excelforum.com/showthread.php?threadid=48436 ...

Need Help #2
I have recently fallin' on hard economic times, I would really like you all to send me donations so I may purchase the new Office 2003 Professional Suite next month. Please send funds to Excel Guru P.O. Box 321 Grosse Pointe, MI Thank you. What's the zip code there? >-----Original Message----- >I have recently fallin' on hard economic times, I would >really like you all to send me donations so I may >purchase the new Office 2003 Professional Suite next >month. Please send funds to > Excel Guru > ...

Need better way to provision AD accounts that are to have mailboxes
The exchange admins in my company have come up with a way for the sys admins to assign the user's mailbox store location. It's based off of last name. For example, if your last name starts with letter A, you go on Storage Group 1, Mailbox Store A, if your last name starts with letter B, you go on Storage Group 1, Mailbox Store B, if your last name starts with letter E, you go on Storage Group 2, Mailbox Store A, etc.... This is, I believe, and administrative nightmare. Is there a better way to provision AD accounts that are to have mailboxes? If it works for you and them, then al...

monthly budget/check registry spreadsheet formula
I'm working on a monthly budget/check registry spreadsheet. What I want to do is have the check register link to the allowed balances in the monthly budget. I have two fields in my monthly budget for each item. A projected cost and actual cost. I want to link entries in the check registry to the actual cost of the fields in the monthly budget. I plan to use account numbers to link these fields to each other. For example . . . Say I buy groceries for $50. When I enter this into the check registry I enter all the necessary information along with an account number that links this tra...

First row in Selection range (first index of a cell)
Hello, I have a problem with selecting first cel in selection Range or return an index of the first cell in Selection Cell. I have something like this: .................... Range1.Select "and here I want to Select the first range in selection Range1" ............. I there any special function of finding first cell in selection range or returning an index of the first cell?? Thanks for answet Marcin Maybe range1(1).select or range1.cells(1).select or range1.cells(1,1).select mar_male@wp.pl wrote: > > Hello, > I have a problem with selecting first cel in selection...

Help Creating A Formula #2
I need to make a formula for alook up. The first spreadsheet i inventory, the second one is pg 103 strapping. I want to enter the fee and inches for pg 103 on the inventory and have it look up the feet an inches on strapping chart and enter the appropriate gallons in column Attachment filename: p & g 101 strapping.zip Download attachment: http://www.excelforum.com/attachment.php?postid=50210 -- Message posted from http://www.ExcelForum.com ...

Need an unbound text box and command button (Search)
I need to create an unbound text box with a command button (Search) on my form which will enable the user to find the current record for a space no. I would like the user to only have to type in the space no. My fields are: SpaceNo Current "Yes" I am not experienced at writing code. Thanks so much. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200911/1 On Wed, 25 Nov 2009 23:09:36 GMT, "Uschi via AccessMonster.com" <u25116@uwe> wrote: If you don't write code, it may be easier to instruc...

Time duration formula
I would like to know the formula to work out my employees employment duration, from a given start date to the current day. many thanks Take a look at DATEDIF. Explained at http://www.cpearson.com/excel/datedif.htm -- HTH RP (remove nothere from the email address if mailing direct) "Ska" <Ska@discussions.microsoft.com> wrote in message news:B10818AE-1CAD-4CE5-BDED-BC0DDC96C876@microsoft.com... > I would like to know the formula to work out my employees employment > duration, from a given start date to the current day. > > many thanks Thanks Bob, I have us...

Excel formula help #4
Hello! Can somone help me with this formula 2004:21-0-1-1-11 in this cell i wan't to get out "21" 2005:2-0-0-0-1 in this cell i wan't to get out "2" My result from the formula is "-21" and ":2-" is there a formula that i can use on both and get the result "21" and "2"? I want to add the together at the end and get the result "23" but with : and - i only get error. -- johhny ------------------------------------------------------------------------ johhny's Profile: http://www.excelforum.com/member.php?act...

Does Multi-Field Index Work For Date/Time Values Only
I created a MS-Access DB table with the following 3 columns: ColID - PRIMARY KEY Col1 - Number Col2 - Date/Time Next I created a multi-field index using Col1 & Col2. I entered the following row in the 1st row: 1 5/10/2007 Next when I tried to add the above row again, as expected, I wasn't allowed to enter the same row. Next I entered the following 2 rows: 1 5/11/2007 2 5/10/2007 Both the rows were accepted. After this I deleted the 3 records, went back to the design view & changed the data type of Col2 from Date/Time to Number keeping the multi-field index...

CONCATENATE text to create a formula to be evaluated
Hi, I am wanting to concatenate a set of text to create a formula. I have done so below. =CONCATENATE("=MAX('",O1,"'!A3:A65536)") The result is: =MAX('2009'!A3:A65536) ....but it treats this as a text string when I try to use INDIRECT Cell O1 contains the year minus 1. In this case O1 = 2009. All of my worksheets are named as a year e.g. 2007....2008....2009...2010. I am entering this on sheet 2010. Now the real question: How do I make another cell evaluate this string as an actual formula and spit out the highest number for ...

text in formula
Help!!! Is there a way to have a formula where a cell with text is in it, but it is not included in the formula. Like I have a random cell that appears sometimes within the range but because it is text and I do not want to include it in the formula. Is there a certain "symbol" that could be used? HELP!! Hi maureen, It depends on what the actual formula is, but *some* functions ignore text entries. You could use the ISNUMBER function to include only those entries that are numbers. Post your formula for a more detailed reply. Biff >-----Original Message----- >Help...

"with <string> in sender" clause works on substring (matches on too many senders)
Outlook 2002 SP-3 I don't send myself e-mails but spammers would like to pretend that I do. They often must put something in the From header to be RFC compliant, so they throw in the same e-mail address as they shoved in the To header. Say I have an account called smith@domain.tld. I define the rule: Apply after receiving a message with smith@domain.tld in sender permanently delete stop processing more rules Looks good until you realize the match is on a substring rather than an exact match on the sender's full e-mail address. The messages do get deleted if I sen...

Manual Sales Methodology -- Need Help
Hi, Here is my situation. We are trying to implement a manual sales methodology. Basically I have a dropdown list called "Sales Stage" with values entitles "Stage 1", "Stage 2" and "Stage 3". I need the values here to effect the sales pipeline and the sales pipeline report. The other issue is that a lead can advance or retreat in the sales cycle. Meaning it could go from Stage 1 to Stage 2 and then back to Stage 1 or could go Stage 1 to Stage 3 and then to Stage 2. I know I need a workflow setup (as just effecting the "stepname&quo...

counting using multiple criteria
Hi, I have a problem for which I can't seem to find a working solution. On the one hand I have a vertical table with employee names, the number of years they have been with the company, and all this sorted by the employee's age (in years). This table will be updated from time to time, and as such it doesn't have a fixed length. On the other hand I have a table which shows the number of employees in a particular age category (-20, 21-25, 26-30, etc) horizontally, and the number of years they have been with the company (again in groups: 0-5 yrs, 5-10 yrs, etc) vertically. ...

need formula help
I'm trying to have a formula do 3 scenerios in one cell and have a partial formula working: IF(AND(G39>30, G39<=7),3000) which equates to if cell G39 is greater than 0 and less than or equal to 7 use 3000. I would like to continue the formula that if G39 is greater than 7 and less than 14 use 5000 and if G39 is greater than 14 use 7500. How can I do this? Hi, =IF(AND(G39>0, G39<=7),3000,IF(AND(G39>7, G39<=14),5000,if(G39>14,7500))) "tom" wrote: > I'm trying to have a formula do 3 scenerios in one cell and have a partial > form...

Formula Help #66
I need help with a formula. I can't figure out how to calculate for missing number. '05 we sold x units, I know we had a X% increase over '04 so what wa the '04 sales? Column A = '05 Columb B = '04 Column C = % increas -- bab ----------------------------------------------------------------------- babz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2781 View this thread: http://www.excelforum.com/showthread.php?threadid=49307 '04 sales = '05 sales / 1.X% increase Vaya con Dios, Chuck, CABGx3 "babz" wrote: >...

Copying formulas to another worksheet
Can anyone please help me, I am trying to copy a formula =(sump1:p3) from one worksheet into a new worksheet. When I go to paste the formula the only thing that comes up is =SUM(#REF!). Can I even copy and paste a formula from one worksheet into a new worksheet? and if so how? Thanks When you paste a formula it relatively changes the cell references to suit where you have pasted it to. EG. If you pasted =sum(P1:P3) from cell P4 to cell B4 it would change to =sum(B1:B3). However, if you pasted it to cell B2 it could not reasonably change the references. If you are pasting because you...

Creating Formula with Embedded Variable ID
I need to create a single formula for my workbook that references cells from various tabs (over 20) in another workbook. I want to copy this formula into multiple tabs within my workbook. My workbook and the external workbook have the same number of and identically named tabs. I want to embed a variable into the formula that will take on the name of the tab that the formula is copied to. I imagine the formula would look something like this: ='K:\Sales Reports\[Ohio Team, Mar-21-2004.xls]variable_identifying_the_tab_goes_here'!D1 I know I can use the following formula to derive th...

Amount in Words Complete Right Formula
give me complete formula See http://www.xldynamic.com/source/xld.xlFAQ0004.html -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Naveed" <Naveed@discussions.microsoft.com> wrote in message news:29B7C0D6-1DED-4321-B1EB-CA6DDCE73852@microsoft.com... > give me complete formula > ...

Formula to reference another worksheet, locate data, then record i
Hi All, It has been over 10 years since I did my Excel studies and I've unfortunately forgotten everything I haven't used regularly. My office has recently upgraded to Office 2007 (upgrade not being the descriptor I'd have chosen!) and I'm struggling with Excel. I've found my way around most issues, but I'm REALLY stuck now and suffering Friday-itis on top of it all! Essentially I have a multi-sheet workbook for my debtors ledger. Each page has separate columns for the customer numbers, names, total debts in each age range (7 days, 14 days etc), totals and lastl...

How to reference workbook name in hyperlink formula?
In order to simplify the maintenance of workbook formulas I need to reference workbook name in hyperlink formula =[myWorkbok.xls]mySheet!$E4 in this way =[A1]mySheet!$E4 given the file name stored in cell A1 Cell A1 : myWorkbook How do I write the formula to make it work? Will it work even if the referenced workbook is closed? Regards Frank Krogh The function you'd want to use is =indirect(). But =indirect() won't work if the sending file is closed. Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm ...

Matching and Copying entries
I have two worksheets, on sheet1 there are 4 columns, columnA is the customers account number. On sheet2 I have 5 columns and again columnA is the customers account number. I want to keep all records from sheet1, but I want to add two extra columns of data that is currently held on sheet2. So I think I need to compare the customers account numbers between sheet1 and sheet2 and where they match copy over columns C and E from sheet2 to two new colmns on sheet1. What would be the best eay to approach this problem. (I have approx 20,000 rows on sheet1 and nearly 50,000 on sheet2.) You say tha...

print preview does not match page i am working on
In this white space (not the subject line), let us about the problem in more detail If print preview is showing only part of the worksheet, then you need to reset the print area in the File | Print Area dialogs best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "gillygalloo" <gillygalloo@discussions.microsoft.com> wrote in message news:9739F8B9-CBC6-4722-88FA-AEC01F6348FC@microsoft.com... > ...

You need more memory or system resources
I have outlook 2000 connected to exchange server 5.5. Whenever I right click on the Outlook icon and go to properties, I get the error message "you need more memory or system resources." I scoured the internet and the info I'm getting is that it has something to do with outlook express 5 and it's a mapi setting that should be unchecked. I have outlook express 6 installed and I don't see that option anywhere. I turn off and on my computer, I don't have any programs running on startup and the first thing I do is try to get into properties and it still gives me th...