Conditional formatting query #2

Hi,

I thought I knew how to do this, but it turns out I don't.

If I have names in column A (eg Alan, Bob, Cathy), and values in column B 
(see below), is there any way I can use conditional formatting to change a 
cell in A, based on a value in B?

    A        B
1 Alan    4
2 Bob    1
3 Cathy  3

Essentially what I'd like is to highlight the name, if the value is 3 or 
over.

I know how to change the  cells in B using conditional formatting, but can't 
figure out how to change A cells.

Thanks,
Fiona 


0
11/2/2004 11:31:16 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
212 Views

Similar Articles

[PageSpeed] 26

Select all the cells you want Conditionally formatted.
Try this with Formula Is selected in teh Conditional Formatting Dialog:
=B1>=3
This example has Alan in A1.

tj

"Fiona O'Grady" wrote:

> Hi,
> 
> I thought I knew how to do this, but it turns out I don't.
> 
> If I have names in column A (eg Alan, Bob, Cathy), and values in column B 
> (see below), is there any way I can use conditional formatting to change a 
> cell in A, based on a value in B?
> 
>     A        B
> 1 Alan    4
> 2 Bob    1
> 3 Cathy  3
> 
> Essentially what I'd like is to highlight the name, if the value is 3 or 
> over.
> 
> I know how to change the  cells in B using conditional formatting, but can't 
> figure out how to change A cells.
> 
> Thanks,
> Fiona 
> 
> 
> 
0
tjtjjtjt (488)
11/2/2004 11:41:03 PM
Hi Fiona,
The cells that get colored are based on your selection as stated.
     Select Column A
The formula is based on your    **active cell**.
     Your active cell would be A1 is you are looking at the top of the
     worksheet.     So    A1 being the active cell would
     have the formula that you enter as  provided in your last reply.   =$B1>=3
Actually by using $B  instead of B    you could select column A&B
if you want both columns to be colored,  or if you want the entire row colored
you would have selected all of the cells   (Ctrl+A,  except in Excel 2003)

That means that  A2  would be working with a C.F   of  =$B2>=3
etc. on down.

see Conditional Formatting
  http://www.mvps.org/dmcritchie/excel/condfmt.htm


---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"tjtjjtjt" <tjtjjtjt@discussions.microsoft.com> wrote in message news:96735A1A-60D9-4C52-BF83-913A3B0322E7@microsoft.com...
> Select all the cells you want Conditionally formatted.
> Try this with Formula Is selected in teh Conditional Formatting Dialog:
> =B1>=3
> This example has Alan in A1.
>
> "Fiona O'Grady" wrote:
> > I thought I knew how to do this, but it turns out I don't.
> >
> > If I have names in column A (eg Alan, Bob, Cathy), and values in column B
> > (see below), is there any way I can use conditional formatting to change a
> > cell in A, based on a value in B?
> >
> >     A        B
> > 1 Alan    4
> > 2 Bob    1
> > 3 Cathy  3
> >
> > Essentially what I'd like is to highlight the name, if the value is 3 or
> > over.
> >
> > I know how to change the  cells in B using conditional formatting, but can't
> > figure out how to change A cells.


0
dmcritchie (2586)
11/3/2004 12:00:41 AM
Reply:

Similar Artilces:

ALT + ENTER #2
I cant seem to figure out what pressing both ALT + ENTER does. I am working on a spreadsheet, and Im supposed to click on a cell and put my cursor before a certain word in the cell, and press ALT + ENTER. I know its supposed to show only the word after the cursor, but its not doing that...it does nothing at all. Help! It will insert a carriage return, so if you have xxxxx xxxx and you put the cursor between the 2 strings and do alt + enter it will change to xxxxx xxxx -- Regards, Peo Sjoblom "Heather" <heather.l.nelson@sfcc.edu> wrote in message news:276CFDD9-6612-47...

Cond Formatting
Hiya, I'm in cell G5 which is where I have "S" for Saturday, the next cell is "S" for Sunday etc.... 1) What I would like to do his put a shade of grey in the column starting at G7 to say G120, and then I'd obviously copy it to the remainder of the "S's." 2) I also have other CF in that column, such as if "W" is entered it changes to a green background (but no CF in G5), will one over ride the other, and or will there be a conflict... that is once I learn how to do question one.... :-) Thank you, P.S. I like this newsgroup, it gets t...

XML Schema validation #2
Datetime field validation failed on fields with null data, why is that? Should I use this nillable="true" tricks in Schema? Thanks, Lenny Lenny Woodbridge wrote: > Datetime field validation failed on fields with null data, why is that? Empty value is not valid value for xs:dateTime type. See http://www.w3.org/TR/2001/REC-xmlschema-2-20010502/#dateTime for more info. -- Oleg Tkachenko http://www.tkachenko.com/blog Multiconn Technologies, Israel ...

new message window (Microsoft Word) opens 1/2 way!
When we go to compose a new message in Outlook 20000, the window only opens up 1/2 way and we have to maximize it. There have been a few times that the window will open up all the way but not sure if there is either a regestry setting or local setting that would control this feature??? Thanks! On hands and knees, Paul crawls to SilverRaven and begs: >When we go to compose a new message in Outlook 20000, the >window only opens up 1/2 way and we have to maximize it. >There have been a few times that the window will open up >all the way but not sure if there is either a re...

Using data from cells in a Query to a MS Access Database
I need to use a MS Excel 2003 spreadsheet to perform a simple set of calcs. One piece of information I need is stored in an Access Database. If I could have, I would have converted the spreadsheet to an MS Access database and programmed the query to use information from text boxes. However, not all employees in the company have MS Access on their computer. So I need to use an external query in Excel to look at the MS Access Database. I can do that pretty easily. The Query Wizard walks you right through it. However, I need to make it so that the criteria for the query is drawn ...

numbering #2
Is it possible to apply numbering (invoice or booking numbers) to sheets inside of a book. Four people using the same books and we want to put number on the sheets without becoming a number management nightmare Like adding a series of numbers like invoice number Each sheet having it's own number in the series but not repeting on different sheets. Basically generate an invoice number on the sheet when copied from a templete or at print time ...

calculate dates #2
I am trying to create an application to calculate a date that is 2 weeks from today but I never want the date to be a weekend or holiday. The results I want to run on someones desktop without them having to do anything. Any thoughts? Use =WORKDAY(TODAY(),14) WORKDAY is part of the Analysis Toolpak add-in, so you may to install that -- HTH RP (remove nothere from the email address if mailing direct) "kevin.raway@lifefitness.com" <kevinrawaylifefitnesscom@discussions.microsoft.com> wrote in message news:A790A02C-0A02-42F0-9684-719436798CD6@microsoft.com... > I a...

CRM 1.0 XML import to CRM 1.2
Does anyone know how to import a CRM 1.0 XML into the CRM 1.2 env? The deployment manager rejects the 1.0 XML file during the import process - ERROR: does not match version of the database. So is there anyway work around this? Gues I could modify the XML version but.... Tony, From what I've heard from others, there are differences in how the content of the files are structured, so you can't really do what you are trying. Your best bet is to upgrade the install and then export the customizations to create a new XML file. On Tue, 10 Feb 2004 13:43:52 -0800, "Tony Nguyen&q...

format problem importing quicken files from Bank
Hi I have Money 2001 never used it until today. I am in Italy and my bank allows me to export bank statement in a quicken file. However when I import it into money - money misreads the format and takes the first punctuation mark as the decimal point. i.e. the amount euro 1.234,56 imports as 1,23 euro's instead of one thousand euros. My regional settings are set to italian with , being the decimal and . being a separator - however money takes the first , or . as the decimal Any ideas on how to fix this? regards Ski ...

Excel cell problem #2
I have a workbook I have created (I am a novice), which for some reason, when I want to add or delete a cell in ANY column, I no longer have the option to shift cells up or down, it will only allow me to shift the entire row up or down. Anyone have any idea what I am doing wrong here? I have some formulas set up to keep counts for me, nothing real special. Thanks in advance. -- Thanks Steve Have you applied Data|filter to that worksheet? Try data|filter|show all (to see everything) then try it out. Steve wrote: > > I have a workbook I have created (I am a novice), which for so...

format cell to allow listing
How do I make a list within a cell (when I press enter, it goes to the next cell below- is there any other way besides 'wrap text' and putting in spaces? To create a manual line break, use: <Alt> <Enter>. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "cy" <anonymous@discussions.microsoft.com> wrote in message news:1536c01c3fa2c$66a21870$a501280a@phx.gbl... How do I make a list within a cell (when I press enter, it goes to the...

custom currency format for Indian rupees
How to format a comma from the right of the decimal point in the third position, then 5th then 7th ie for Indian curency for Lakhs, Crores etc Here is a format that you can use [>=10000000]##\,##\,##\,##0.00;[>=100000]##\,##\,##0.00;##,##0.00 -- HTH RP (remove nothere from the email address if mailing direct) "seenu" <seenu@discussions.microsoft.com> wrote in message news:B95D00EB-F863-4453-AC46-9D40B40AEE6F@microsoft.com... > How to format a comma from the right of the decimal point in the third > position, then 5th then 7th ie for Indian curency for L...

book query
I would like a recommendation for a good book on Excel. I would place myself as an intermediate who has just started to learn some formulae. I use Excel XP, 97 and also Excel X on my mac I would like it to be not too heavy going so as not to put me off learning Any help would be appreciated Bobby For information and examples on Excel's functions, you can download Peter Noneley's Function Dictionary: http://homepage.ntlworld.com/noneley/ or Norman Harker's information on functions: http://www.contextures.com/functions.html and John Walkenbach's book on formul...

Log-log charts #2
Hi, I'm using Excel 2002 and am trying to create a simple log-log plot. Making the y-axis logarithmic is easy - it's a check-box option under Scale tab of the Format Axis dialog. But for the X axis the dialog does not have that option. My XY data is valid (all positive values). There's web sites that say you can do this (e.g. http://cstl-cst.semo.edu/venezian/Utilities/graphs.htm). Why doesn't my Excel support it??? Any help is appreciated. Walt Hi Walt, Sounds like your chart maybe a line chart (x-axis is category) instead of true xy scatter. Right click the chart...

counting rows depending on 2 selective values
Hi, well, I have lots of data and need to do some statistics on it. For that I need to count the number of rows "H" where a string value "X" is written but only if these rows also have cell which is NOT empty in column "E"... the if stuff drives me crazy (especially as i have a spanish version...) any help appreciated :) -- Acid-Sky ------------------------------------------------------------------------ Acid-Sky's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26502 View this thread: http://www.excelforum.com/showthread.php?thr...

Keep conditional format when "show pages" from Pivot table
When I use "show pages" to drill down the data into pages from Pivot table, I find the conditional format on pivot table is missing. I have to make conditional format again to every page. How to keep the conditional formatting when I use "show pages" from pivot table? Maybe you could just apply Data|filter|autofilter to your original data range and filter to show what you need to see. Angus wrote: > > When I use "show pages" to drill down the data into pages from Pivot table, I > find the conditional format on pivot table is missing. I have to m...

Automatic Categorization #2
I am new to Money 2007 Deluxe. I've gotten all of my accounts set up and downloading properly and noticed that Money will automatically try to assign a category to transactions in my checking account register. (ie a checkcard at McDonalds is put in the "Dinig Out" category) Is there a way to set this up for the ones it misses and then have it process all entries in the register with that information? Specifically I have a place that I do a lot of business with that will show as "CHECKCARD" + 4 digit date + payee. I would like them all to come in as just the payee and ...

Excel 2003 Upgrade
If I only have Microsoft Works Suite 2004, and I purchase the Excel 2003 upgrade, will I get the full functionality of Excel, or just an upgraded Works spreadsheet program? I went to the Microsoft site, and it states that Works Suite 2004 qualifies for Excel 2003 upgrade eligibility, but I am not really sure what that means. Thanks. Steve ...

Firewire communication #2
Hi guys, I am trying to interact with a camera through firewire. I wrote a code based on the example given in MSDN. When I execute the code it always return the value 1 no matter whether the device is connected or disconnected. Please let me know what went wrong, i couldnt figure out the problem so for. Advanced Thanks - Mike My code is as follows. -------------------------------------- void CFireDlg::OnDeviceNotify() { // TODO: Add your control notification handler code here CString cs; bool bDevInterResult=FALSE; HDEVNOTIFY *hdn=NULL; bDevInterResult = DoRegisterDevi...

Unrecognizable database format
I am attempting to convert an Access 97 Db to an Access 2000 Db. It seems to convert but when I try to launch the application I get an "unrecognizable database format" error. The computer has Windows XP as the operating system. Any suggestions? I've seen this error message when trying to open an Access database with an "older" version of Access. You didn't say which version of Access you are using on the WinXP machine, nor which version of Access you are using to do the conversion. -- More info, please ... Jeff Boyce <Access MVP> >-----Origin...

Date format with day of week and date
I have Excel 2003 and want to figure out how to format a cell where the date and day of week appear...i.e. Wed 10/19/2005 I know how to make the date formats change but cannot find one that offers both the date as well as the day of week...if there are no options for it, what is the format for customizing it to do this? Thanks! Kathy One way is to use a custom format DDDD M/DD/YYYY "Kathy" wrote: > I have Excel 2003 and want to figure out how to format a cell where the date > and day of week appear...i.e. Wed 10/19/2005 > > I know how to make the date format...

How do I save an excel spreadsheet in pdf format (Office 2002)
I've done this before, but in a different environment. Not sure if I've forgotten how, or if it's not supported in this version. Thanks It has never been supported although there are add-ins that install printer drivers that will save as PDF primopdf is a free one then adobe acrobat (not the reader) has of course this and Office 12 will have it built in -- Regards, Peo Sjoblom (No private emails please) "Shaundee" <Shaundee@discussions.microsoft.com> wrote in message news:2F09C8D8-1F35-491B-8A4C-781E57C35D53@microsoft.com... > I've done this b...

Percentages #2
I need to set up a sheet whose columns will show red when cells between ..005 and 6.99, green when >=7 and white when 0. I used conditional formatting to achieve these results.<br><br> However, I also need a column that will take the number of green cells and determine what percentage they constitute of the total of red and green cells. I used COUNTIF and divided it by number of columns to arrive at percentage, but this is a rigid formula. I need a formula which calculates the percentage of green cells (>=7) but which adjusts according to the number of blank cells(0).&l...

cannot use wildcards in LDAP query
I am using genifax and two of the tools they provide error out when using wildcards as part of an LDAP query in the production envioronment. I also tried the tool against servers running LDAP in different sites but they fail. I tested this tool against a lab machine all the same service pack/patches and it works correctly. Any ideas? sorry this is a 5.5 server running sp4 on nt4 sp6a "Charles Hibbits" <Charles_hibbits@hotmail.com> wrote in message news:%23v7mrVmJEHA.4052@TK2MSFTNGP11.phx.gbl... > I am using genifax and two of the tools they provide error out when using...

Data Connection: Access Query to Excel How to get rid of header ro
Hello. I have created a data connection in Excel to an Access query. It does exactly what I need however the header rows of the query are imported as well and all I want are the data in the simplest way possible. Thanks in advance. It depends on how you setup the data connection. If you're doing it in VBA using ADO and a reference to the query, the default is to not import the field headings. If you're doing it without VBA, using Get External Data on the Data ribbon in Excel 2007 you'll get the headings. Here's a short example using Micros...