Excel 2007 CF Multiple criteria

I've always thought CF was one of the more understandable tools used
in Excel. Until now. Using Excel 2007.
Trying to hilight a cell if it's contents are the same as another cell
(Lets say A1 same as B1.).  Also want to ignore the CF rule if either
cell is blank.
It's driving me bonkers.  Ideas?
TIA.
Pierre
0
Pierre
1/12/2010 6:35:06 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
743 Views

Similar Articles

[PageSpeed] 54

Select A1:B1
Home tab/conditional formatting / highlight rules /duplicates
works a treat
best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Pierre" <cowguy@aol.com> wrote in message 
news:96eb3105-8017-4e7f-8173-4643991d2ad0@j5g2000yqm.googlegroups.com...
> I've always thought CF was one of the more understandable tools used
> in Excel. Until now. Using Excel 2007.
> Trying to hilight a cell if it's contents are the same as another cell
> (Lets say A1 same as B1.).  Also want to ignore the CF rule if either
> cell is blank.
> It's driving me bonkers.  Ideas?
> TIA.
> Pierre 

0
Bernard
1/12/2010 7:53:47 PM
Bernard, thank you.  That does work fine for the whole sheet.  What if
the ranges are comparing values in Col:A versus the corresponding
value in col: V?  (Lets just say A5: vs. V5.)  Again, ignoring the
rule if either contains a blank?

Thanks so much.

Pierre

On Jan 12, 1:53=A0pm, "Bernard Liengme" <blien...@TRUENORTH.stfx.ca>
wrote:
> Select A1:B1
> Home tab/conditional formatting / highlight rules /duplicates
> works a treat
> best wishes
> --
> Bernard Liengme
> Microsoft Excel MVPhttp://people.stfx.ca/bliengme
>
> "Pierre" <cow...@aol.com> wrote in message
>
> news:96eb3105-8017-4e7f-8173-4643991d2ad0@j5g2000yqm.googlegroups.com...
>
>
>
> > I've always thought CF was one of the more understandable tools used
> > in Excel. Until now. Using Excel 2007.
> > Trying to hilight a cell if it's contents are the same as another cell
> > (Lets say A1 same as B1.). =A0Also want to ignore the CF rule if either
> > cell is blank.
> > It's driving me bonkers. =A0Ideas?
> > TIA.
> > Pierre- Hide quoted text -
>
> - Show quoted text -



0
Pierre
1/12/2010 8:22:47 PM
On Jan 12, 2:22=A0pm, Pierre <cow...@aol.com> wrote:
> Bernard, thank you. =A0That does work fine for the whole sheet. =A0What i=
f
> the ranges are comparing values in Col:A versus the corresponding
> value in col: V? =A0(Lets just say A5: vs. V5.) =A0Again, ignoring the
> rule if either contains a blank?
>
> Thanks so much.
>
> Pierre
>
> On Jan 12, 1:53=A0pm, "Bernard Liengme" <blien...@TRUENORTH.stfx.ca>
> wrote:
>
>
>
> > Select A1:B1
> > Home tab/conditional formatting / highlight rules /duplicates
> > works a treat
> > best wishes
> > --
> > Bernard Liengme
> > Microsoft Excel MVPhttp://people.stfx.ca/bliengme
>
> > "Pierre" <cow...@aol.com> wrote in message
>
> >news:96eb3105-8017-4e7f-8173-4643991d2ad0@j5g2000yqm.googlegroups.com...
>
> > > I've always thought CF was one of the more understandable tools used
> > > in Excel. Until now. Using Excel 2007.
> > > Trying to hilight a cell if it's contents are the same as another cel=
l
> > > (Lets say A1 same as B1.). =A0Also want to ignore the CF rule if eith=
er
> > > cell is blank.
> > > It's driving me bonkers. =A0Ideas?
> > > TIA.
> > > Pierre- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

I figured it out.
Formula is: =3DAND($E2>0,$E2=3D$J2) in this instance.  Format as desired.

Thanks for all.

Pierre
0
Pierre
1/12/2010 9:51:50 PM
Reply:

Similar Artilces:

PDF Table to Excel Spreadsheet
This has been asked in numerous places all over the web but I have not found a workable solution. We have a mainframe system that generates PDF reports for billing. However, few people in our organization these days speak mainframe. We need Excel to do calculations on the data. I began working on a method for converting using UEdit Macros and an Excel macro because the fields are fixed-width. My attempts have been unsuccessful. Here is what I want to accomplish: -Copy a region of text from a PDF -Paste into Excel -Break the values out into the appropriate cells. http://www.google.com/sea...

Excel Drop Down Boxes #2
Question; does anybody know how to add additional data to a previous drop down box? I have created a form that has several drop down boxes a while ago, and due to some information that has been change I need to input more info in the drop down boxes but for some odd reason I cant remember how I executed these actions!! If it helps I am using Excel 2000. -- CRS ------------------------------------------------------------------------ CRS's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27444 View this thread: http://www.excelforum.com/showthread.php?threadid=46...

HELP! Need to save in Excel 2.1 format
Hi I have excel 2007, and the datasystem here only accepts excel 2.1 format files. How can i convert or save in excel 2.1 format? I'd really appreciate any input, even link to some external convert program. Thanks Hi, Why you don't try to save it as excel 5.0 It should work when saving as scroll almost to the bottom there you will find excel 5.0 "Poppe" wrote: > Hi > > I have excel 2007, and the datasystem here only accepts excel 2.1 format > files. > > How can i convert or save in excel 2.1 format? > > I'd really appreciate any input,...

No PocketPC support in 2007?!?
Lame!!!! Where is the official place to go to send MS some feedback on the lack of PocketPC support for 2007? "Matt Miladen" <Matt Miladen@discussions.microsoft.com> wrote in message news:FF9E7EFB-8C6B-4DA5-B1BA-798F5527DF0B@microsoft.com... > Lame!!!! > > Where is the official place to go to send MS some feedback on the lack of > PocketPC support for 2007? LOL I don't know if you even want to try, Matt. I tried and got some "lame" spiel back from a CSR that WinMob5 was an OEM product and that I'd have to go to the OEM for support. ...

Outlook 2007 doesn't appear in Set Program Access and Defaults
I just installed Office 2007 Pro but for some reason Outlook 2007 doesn't appear in my "set program access and defaults". Outlook Express appears as my only option and Outlook Express appears in the e- mail slot on my start menu. Any ideas why? You must uninstall any prior version of Outlook and re-run setup using a = custom installation and make Outlook available. --=81 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. =20 After furious head scratching...

Excel should provide an easy way to switch a column into a row, n.
A very useful feature for Excel would be the ability to easily switch a column of data into a row. This would allow a column of entries to be used as a row of headers, for example. Excel already allows switching a column into a row in the charting area, but not in the spreadsheet. One can work around this by exporting the column into Word, changing the delimiter between fields from a paragraph mark (normally hidden) to a comma, then re-importing the data into Excel - which will then be as a row. This would also allow Access to import the former column into a row, which could then be...

How do I use excel names with INDIRECT with charts
Hello, I want to create a Chart that does not directly reference cell-ranges (i.e. A1:A6), but excel-names that make the reference sheet-independent. My aim is to be able to copy one chart to other worksheets, which have their dataareas at the same places like the source-sheet. Problem: This works fine in cells but not in charts Excel name definition: =INDIRECT("R3C2";0):INDIRECT("R3C5";0) Any idea about this? Thanks in advance, Holger. You have to include the sheet name in the final formula that you want XL to use. -- Regards, Tushar Mehta www.tushar-mehta.co...

multiple mailbox
how to create multiple mailboxes at a time in Exchange2000 & 2003 If you have already created the accounts and are just mail enabling them then you can hightlight all of the affected accounts and right click, Exchange Tasks and Create Mailbox. If you're scripting the account creation from scratch then you can add them that way. Are you scripting? with what? >-----Original Message----- >how to create multiple mailboxes at a time in Exchange2000 & 2003 >. > ...

Vlookup Multiple Cells?
I want to use Vlookup to obtain the first 5 cells to the right of the 'Lookup Value' in the 'Table Array'. I know I can do this with 5 different Vlookup functions, but is there a way to 'Lookup' a range of cells? Steve One way =VLOOKUP(A1,$B$1:$G$100,{2,3,4,5,6},0) you have to select 5 cells across, then click in the formula bar and enter the formula there, and then enter the formula with ctrl + shift & enter -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjoblom "Steve Almond" <steveal@nildram.co.useless&g...

Deleting Multiple lines
Hi I have a spreadsheet which lists all invoices that are due to be included in a weeks payment run. Is there anyway i can set up the spreadsheet to delete multiple lines for each supplier so all i am left with is a list of all suppliers i am going to make a payment to that week. Thanks Two ways spring to mind that could help. i) Create a Pivot Table - In the Data menu there is a wizard - Highlight the range where the data is and follow the steps. On completion you will select the values as the data range suppliers as Rows and invoice date as column heading. This will sum the totals ...

Setting up Outlook for multiple users on the same computer.
Hi were using outlook on a pc running xp and two seperate users. One user can read email with no problem and the other can't log on to Outlook. Are there any settings that I may have missed? We always used Outlook Express and switched so this is kinda new to us and any help is appreciated, thanks. What happens when the second user tries to open Outlook? Is he opening it from a shortcut? Where is the shortcut? -- Jocelyn Fiorello MVP - Outlook *** Replies sent to my e-mail address will probably not be answered -- please reply only to the newsgroup to preserve the message thread. ...

Excel
i use Excel with office xp. i have a form we have made that requires client Name, Address, Phone, Fax etc etc etc... currently i keep all those contacts within my outlook contacts... is there a way i can type in the client business name in that cell, and have excel do one of the following: autofill with available address, phone, fax, PC, City/Town, contact... or... give me a choice of available contact... keep in mind, the Address, Phone, Fax, PC, City, Contact Name are all different rows and colums... Any thoughts Big One method....... Export your Contacts from Outlook as a *.CSV f...

Excel VB Code Error
I am trying to create a .csv file from an Excel workbook using a VB module and am wondering if someone can tell me what's wrong with the below code: Public Sub CSV2() Application.DisplayAlerts = False Sheets.Add ActiveSheet.Name = "Temp_10001" For Each ws In Worksheets If ws.Name <> "Temp_10001" Then ws.UsedRange.Copy Sheets("Temp_10001").Range("A1").Activate x = ActiveSheet.UsedRange.Rows.Count If x > 1 Then ActiveCell.Offset(x, 0).Select ActiveCell.PasteSpec...

Problems saving excel file from outlook
I have an EU that whenever he saves an excel file from outlook, it will rename the file (by adding the number "2" at the end of the file "file2.xls") and it also changes all the embedding links in the document. (The embedding link of F:\users\file.xls changes to c:\documents and settings\file.xls) Are you setting a Hyperlink base for your links? See Menu > File > Property > Summary tab. Put the original folder location there and when you move or save the file the links will not be renamed. Also assigning a letter to the drive can cause problems (not ...

Multiple Output from Listbox
Can anyone help?! I have a listbox with four options set up so multiple choices ar allowed. I would like output in a seperate sheet that records which o the options are selected. So if the options were 1-4 I would like four columns in my output shee with 1s and 0s or TRUEs and FALSEs for each option. Can this be done -- Message posted from http://www.ExcelForum.com What you want shouldn't be that hard. Was your listbox inserted fro the Control Toolbar or the Forms Toolbar? How are you planning o submitting your selections....Will you click on a submit button or d you want the event ...

to extract multiple values from an array
Hi, I am into Image processing industry, for each job we create unique code in excel, we Process 20 jobs in a day, I want to list all the job Code in "summary of the day sheet" that we complete for the particular day . On Dec 30, 4:56=A0am, ratan h <ratha...@nextgenalbums.com> wrote: > Hi, > > I am into Image processing industry, for each job we create unique > code in excel, we Process 20 jobs in a day, I want to list all the job > Code in "summary of the day sheet" that we complete for the particular > day . Not enough info but, assuming you are...

Excel tab as windows
Is there a way while running Excel to be working on one tab and open another tab as a smaller window that is always on top. So I can enter information in both tabs without having to minimize, maximize, or switch back & forth? Thanks C. Fillmore Hi you could align the windows so you can see both at the same time. But a 'always on top' option does not exist AFAIK -- Regards Frank Kabel Frankfurt, Germany surveyorinva wrote: > Is there a way while running Excel to be working on one tab and open > another tab as a smaller window that is always on top. So I can > enter in...

Referencing Multiple Worksheets using VLOOKUP and INDIRECT
I am trying to use INDIRECT to refer to a range on a given worksheet but am getting mixed results. Would some please inspect these formulas and tell me what I am doing wrong? This example works: If the value in B1 is 1, then the following formula returns the correct value =VLOOKUP($A4,INDIRECT("Sheet"&B1&"!$A$1:$C$3"),2,0) This example does not work: But if the value in B1 is Sheet1 the the following value returns #REF! =VLOOKUP($A4,INDIRECT(B1&"!$A$1:$C$3"),2,0) In real life my worksheet will not have names like Sheet1, Sheet2 but rather words lik...

can cvs files be opened using excel viewer
i have a user who cannot open cvs files with excel viewer but can ope with the full version of excel...anyone experienced this problem and i so, can you pls post the resolution -- darrie ----------------------------------------------------------------------- darriel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2388 View this thread: http://www.excelforum.com/showthread.php?threadid=37522 There is nothing in the description of the Excel Viewer to suggest that it can read anything other than true XLS files. http://support.microsoft.com/default.aspx?scid=kb;...

Am I able to manage multiple email accounts
I have several email accounts including Gmail, HOtmail and corporate. I can't make Entourage mail (2004) list individual accounts or find how to manage profiles. Apple mail works but doesn't allow me to insert a tiff company logo without making it an attachment. Outlook allows for all of the above.<br> Appreciate help > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3285235088_9822952 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit On 2/7/08 1...

how do i search an excel sheet for links to another spreadhseet?
Is there a Find function that allows me to look for links in a sheet? When I can't find links, I'll use Bill Manville's FindLink program: http://www.oaltd.co.uk/MVP/Default.htm Flutie99 wrote: > > Is there a Find function that allows me to look for links in a sheet? -- Dave Peterson ...

Excel Reader scroll bar???
I'm using an Excel viewer to read e-mail attachments from my home office. I don't have Excel installed on my machine, only the viewer. Often I do not have the horizontal scroll bar. I have tried maximizing the window and still no luck. Any solutions for this? Thanks ...

Publisher 2007 and Microsoft Updates
I created a newsletter yesterday - everything fine - use Publisher all the time. Then an automatic update came down this morning and I can't open the file - it says "Publisher cannot open file" Other documents in Publisher are opening - any ideas? http://support.microsoft.com/kb/972566/ -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Kim" <Kim@discussions.microsoft.com> wrote in message news:8754339B-99DC-4D09-83AD-6B34D8215274@microsoft.com... >I created a newsletter yesterday - everythin...

data extraction from excel
I need to pull information from one collumn. If the collumn is comprised of yes's and no's, can excel create a list of just the yes's? Try something like this: Assuming your list is in A1:B100, with A1: Question B1: Response C1: Response C2: Yes D1: Question (the same col heading as A1) E1: Response (the same col heading as B1) Select your list (A1:B100) Data>Filter>Advanced Filter List Range: (already selected) Criteria Range: C1:C2 Click the [OK] button to filter the list in place. OR To copy the matching records to another area: Click: Copy to another location Copy...

batch importing of mailing lists to Excel
I've got a mailing list of around 1000 names, stored in both Word and Entourage like this: First Name Last Name <e-mail address> Once I’ve eliminated the < >, do I then have to individually copy & paste (or type) all the info in three Excel columns? Or is there a simpler solution for batch importing of mailing lists to the Excel spreadsheet? "bgsignal" wrote: > I've got a mailing list of around 1000 names, stored in both Word and > Entourage like this: First Name Last Name <e-mail address> > Once I’ve eliminated the < >, do I then ...