Conditional formatting can do what you want. I'll assume that you are using Office 2007:
- Select the cell you want to format
- On the "Home" tab, click "Conditional Formatting" and select "Manage Rules"
- Click "New rule"
- Select "Use a formula to determine which cells to format"
- Enter "=C2<2" (without the quotes) into the formula box
- Click the "Format" button and select the formatting options you want
- Click OK
You can set multiple rules, and you can change the formatting of one or more cells in response to the value in a completely different cell. Very flexible and powerful.
I think I used a combo of all advice and here is what worked:
Using the conditional format I changed the "condition" drop down to FORMULA IS -
then I put in =IF(K2and selected red from the cell format box.
This worked. If k2 is ,than u2 - the bg turns red ! I have a huge complex spreadsheet that needs "differences" to POP OUT. I can now apply this formula all over the sheet !
Yep!
Once you get used to defining conditional formats based on formulas, you might never use the "quick & easy" selections again! It's one of the little-recognized capabilities of Excel.
I think I used a combo of all advice and here is what worked:
Using the conditional format I changed the "condition" drop down to FORMULA IS -
then I put in =IF(K2and selected red from the cell format box.
This worked. If k2 is ,than u2 - the bg turns red ! I have a huge complex spreadsheet that needs "differences" to POP OUT. I can now apply this formula all over the sheet !
Thanks.
Another thing to remember is if you want to copy and past formulas that are the same distances apart you can remove any '$' from the formula and it will allow you to do this. One thing I've noticed is 2007 adds a ton of $ signs to formulas.
I need to be able to copy and paste the "conditional formating" w/o overwriting the number in the cell.
Example:
Cell C2 is 1.25. When the formula is applied it will turn red. I can copy that cell to other cells and the formula follows, however, it replaces all the number in the new cell with 1.25. I need it to past the formula w/o overwriting the number.
Put the conditional formula in the top cell. When you're sure it is working you then:
Select cell where conditional formula is then scroll down and select all cells below. Click on Format - conditional format again...you will see that your formula is still showing...press ok and your formula copies down w/o overwriting the numbers in the cells.
Originally posted by kenskid:
Poop...another issue...
I need to be able to copy and paste the "conditional formating" w/o overwriting the number in the cell.
Example:
Cell C2 is 1.25. When the formula is applied it will turn red. I can copy that cell to other cells and the formula follows, however, it replaces all the number in the new cell with 1.25. I need it to past the formula w/o overwriting the number.
I hate to confess I'm still using Excel 1997, and I didn't even know one could do this. I didn't expect to learn new things about office programs at a photography site, so thanks for the lesson.
As for copying the conditional formatting, I presume that newer versions of Excel still have the paintbrush (format painter) tool.
I use the formula option If/Then command often, however, I couldn't figure out how to use it to turn a cell a color according to the "answer" of a formula using two other cells ! With a push from people on DPC I was able to figure it out.
There is such a diverse group of people here that someone always knows something....however, I don't know what the paintbrush is in excel ! LOL...
Originally posted by GinaRothfels:
I hate to confess I'm still using Excel 1997, and I didn't even know one could do this. I didn't expect to learn new things about office programs at a photography site, so thanks for the lesson.
As for copying the conditional formatting, I presume that newer versions of Excel still have the paintbrush (format painter) tool.
My wife just purchased Microsoft Office Ultimate 2007 the other day with here school funds trough Microsoft. I was surprised when she got it for $59.95 (Reg. $679.95). You have to be a qualified student but an ultimate deal.
So if your a student and want the ultimate office 2007 for $59.95 here is the link.
Offer good till Dec. 31st, 2009.
I was using Office 2000 and there is a huge difference.
....however, I don't know what the paintbrush is in excel ! LOL...
I don't like the sound of that because it's a tool a use quite a bit. I really am going to have to upgrade one of these days, if only because Microsoft doesn't have support for my ancient version of Office, which does crash from time to time.
....however, I don't know what the paintbrush is in excel ! LOL...
I don't like the sound of that because it's a tool a use quite a bit. I really am going to have to upgrade one of these days, if only because Microsoft doesn't have support for my ancient version of Office, which does crash from time to time.
Its still there. The Format Painter. I use it every day.
I love excel. Some of the formulas I've come up with are awesome and get so much stuff done. I really need to learn VBA though.
AJ, if you are doing advanced formulas like the one you listed below, it would be not be too much of a jump to get into VB. I taught my VB over the course of a few summer internships. Start by recording macros, examining the code and then adapting it to your own needs.
Also there are tons of resources online and solutions to so many different problems that other people have had...
Originally posted by AJSullivan:
Originally posted by GinaRothfels:
Originally posted by kenskid:
....however, I don't know what the paintbrush is in excel ! LOL...
I don't like the sound of that because it's a tool a use quite a bit. I really am going to have to upgrade one of these days, if only because Microsoft doesn't have support for my ancient version of Office, which does crash from time to time.
Its still there. The Format Painter. I use it every day.
I love excel. Some of the formulas I've come up with are awesome and get so much stuff done. I really need to learn VBA though.
Agree, if you use EXCEL a lot it's well worth the effort to learn VBA(Visual Basic for Applications).
There is SO much you can do. In my old job I had macros that changed the file names of an entire directory to a predetermined format with the current date. Had one that would open up any text file and count the number of lines and so much more. You can do ALMOST as much as VB6 can do and EXCEL macros are not limited to working with EXCEL spreadsheets.
Also make sure your macro security settings are set to ask you before running any macros. Macros are capable of doing a lot, deleting files/folders writing to the registry etc.
Yeah, I might snag a book and see what I can learn.
I really wish colleges offered advanced excel cources....like an "EXCEL FOR BUSINESS"
it's really not a bad idea. i use VBA with excel and access everyday at work for some pretty crazy things. if you have a CS background it'll come really easy to you.
Yeah, I might snag a book and see what I can learn.
I really wish colleges offered advanced excel cources....like an "EXCEL FOR BUSINESS"
Yes, and yes!
I'd highly recommend a good book to anyone wanting to learn VBA. It's easy to get started without one if you have at least some programming background (I had one course in BASIC back in High School and one course in FORTRAN 77 in college). At first I just muddled my way through, learned by recording macros and picking the code apart and by reading online resources. Unfortunately this does not teach the object-oriented functionality of VBA, and you *really* should learn that part of it to make best use of VBA. For simple macros, it's not a big deal, but for anything more complex it saves tons of time.
A big second for the idea of "Advanced Office Applications for Business" as a required course for both business and technical majors. It blows my mind when I see recent college grads that have only rudimentary knowledge of Excel, Word, and PowerPoint. These are the basic "tools of the trade" for business communication, and high-level proficiency with them is a *huge* differentiator between those who are just adequately productive, and those who are productive well beyond expectations.
I have a sheet that uses if statements to pull data from another sheet. That if statement essentailly says that if x1 = todays date, then pull that column. It works plenty well.
The issue is that at the end of day, we have to go into that sheet, highlight the info, copy - paste special - values, in order to not lose that data at midnight.
Were trying to come up with a good way to have that data fall into the columns and remain there. The best we've come up with is just adding a macro that does the copy and paste for you, but honestly clicking the macro button is only one step less than just copying and pasting.
Why not just use Matlab? No UI since it's really only for engineering programming, but pretty simple and intuitive compared to Java and others. And you can do some extreme data analysis with it (basically anything mathematic, applied to all your data).
I have a sheet that uses if statements to pull data from another sheet. That if statement essentailly says that if x1 = todays date, then pull that column. It works plenty well.
The issue is that at the end of day, we have to go into that sheet, highlight the info, copy - paste special - values, in order to not lose that data at midnight.
Were trying to come up with a good way to have that data fall into the columns and remain there. The best we've come up with is just adding a macro that does the copy and paste for you, but honestly clicking the macro button is only one step less than just copying and pasting.
Any ideas?
One way is just to modify your "if" formula to test for x1 <= to today's date and then it will be true beginning today, and going forward. The two problems with this are:
1.) If the data in the other sheet gets corrupted, so does your sheet (but also, updates to the source data are automatically reflected, a good thing0
2.) the sheet can get *very* large as the number of formulas increases.
Another option is to create a very simple macro that does the copy/paste values operation when you close the file. That way, all data that you acquired from the time you opened the file is made static at the time the file is closed.
