I’m looking for a function that would be very similar to conditional formatting. I want text that is in one cell to change its formatting (say, become bold) when a number in a another cell attains a certain value (say, becomes greater than 5).
So for example, I want the word “dog” in A5 to change to “dog” when the number value in B7 becomes greater than 5.
The problem with conditional formatting is that it seems to only apply to the same cell that the value is in.
Any functions come to mind that would accomplish this?
You reference the cell with the number but format the color white so it doesn’t show up. You then use the conditional format to change the background AND the font color to be the same so the number remains hidden.
You can use an if statement to create the word “dog” if your values are met and then format that cell font white to hide it. as follows: =IF(B5>5,“dog”,1).
You then conditionally format the cell to change the font to black if it it equals “dog”. don’t type the quotation marks in, just use the word dog.
You could make up to 3 different words appear using conditonal formating by using a nested “if” statement to produce 3 different words based on the values.
Again, not reading your original post correctly. I’m drinking to kill the (back) pain. It’s also hard to see bolding on my laptop screen. It looks like your trying to make the word “dog” bold if the value next to it is greater than 5.
This solution would be cheating but it should work.
=IF(B5>=6,“dog”,"dog ")
Note the space after the second “dog” in the statement. Your conditional format would look for the word “dog” with no space and then make it bold.
Maybe my description wasn’t clear. Let me explain what I’m doing. In the A column, I want to have a list of movie titles. In the C column, I will have numerical ratings for that particular movie. So there will ALWAYS be text in the A column (the name of the movie). I just want the name of the movie to change to bold or italics or something like that when a rating of above 5 has been placed in the corresponding row of column C. For example, once I rate “District 9” as a “7” the text of District 9 turns bold.
I think conditional formatting is just what you want if I understand you correctly.
You already have the text ‘dog’ in A5. Put your cursor on cell A5 and make the conditional formatting equation =b7>5 and the format to apply is bold.
Whenever the value in b7 is greater than 5, the text in a5 should change to bold.
To clarify this, you have to click on the first box in conditional formating and click “formula is”. The default is “cell value is” which doesn’t allow formulas.
I didn’t know you could do this until you pointed it out.
Newer versions of Excel require a few more steps:
Go to Home, Styles, Conditional Formatting, New Rule, Use a formula to determine which cells to format, and then you get to enter the formula and choose the format. I just learned that tonight while researching this question. I have an older version at work where I have done conditional formatting, and a newer version at home where I had not done any conditional formatting, so I had to go find it.
Typical Microsoft mentality. Take something people know how to use and force them to figure it out all over again. I’d like to sneek into Bill Gate’s house and fix it so his kitchen light switch turns the faucet on and the faucet lever opens the garage door.
I think Office 2007 was an attempt at making the program more accessible to new users at the expense of experienced ones.
Indeed, Conditional Formatting is a great example: The 2007 version offers a bunch of pre-set defaults that automatically highlight a cell if some criteria are met, or turn numerical percentages into colored percentage bars, etc., while hiding the manual control in the rules editor. The previous versions just led you to the manual editor directly.
Neither approach is necessarily better or worse, but certainly I’d have appreciated a way of preserving the old interface for already-adapted users like you and I. Three years of 2007 later, I still hate the ribbon system. Grr.
From what I read (since I don’t use Office 2007), the keyboard shortcuts still work the same. so if you were an uber power user and had memorized those, you’d still be fine. Also, I there is a program that will allow you to modify the ribbon to more closely resemble the old toolbars, or just customize them to your heart’s content. It’s company called Addintools , has a 15 day free trial, and costs $40. (They also have a version that just mimics 2003 for $10 less, but I’d say the other is a better buy. Power users often modify their toolbars.)
I wasn’t a poweruser, but I did use the keyboard for most of my tasks. I didn’t memorize anything except the basics and I needed the underlined characters in menus (File, Edit, what have you). Their removal has made 2007 a very mouse-centric experience.
A toolbar should not cost $40. I wish Microsoft wold just make something similar and add it to a future service pack.
OK, I don’t want to think about the times I’ve spent working out ways to do what’s already built in. :o
Conditional Formatting. Pretty nifty.
So far, I’ve managed to refuse 2007, but I know someday they’ll make me switch. I’m dreading it. I don’t have time to relearn entire applications just because MicroSloth got an itch to do something cool and Mac-cy.
