Excel Conditional Formatting

tim1_2 wrote:Excel has a pretty good built-in tool for conditional formatting, but I don't know how to make the value in one cell affect the formatting in a different cell, if that's what you're going for.

Under Conditional Formatting select New Rule and you get this interface:


Click on the last option -- "Use a formula to determine which cells to format". Any cell references in the formula are relative to the cell where you were when you started (in my examples I assumed a heading row 1, then the first cell to format was in cell 2). When you copy the formatting to the cell below, that 2 will automatically change to a 3. The $ in front of the column marker is to make sure that the formula always checks the cell in that column. This way you can copy this formatting to a range of cells in the same row.

wprager wrote:You cannot have a single rule with multiple outcomes.

Sure you can. I do it all the time with statuses to show progress for tasks (Not Started, In Progress, Completed, Error). I have a single conditional statement that sets the colour depending the status. That is the whole point of conditional.

I'm the king of excel

Cmon Big Bad Ev, It's time to learn VBA you're gonna need it later if you want the best jobs  

LeKing wrote:Cmon Big Bad Ev, It's time to learn VBA you're gonna need it later if you want the best jobs  

COBOL baby, COBOL.

shabbs wrote:

wprager wrote:You cannot have a single rule with multiple outcomes.

Sure you can. I do it all the time with statuses to show progress for tasks (Not Started, In Progress, Completed, Error). I have a single conditional statement that sets the colour depending the status. That is the whole point of conditional.

I think we are talking about different things. You can only have *one* format setting for the cell background colour. So if yo need three colors you need three rules (unless you give the cells a "default" colour as the " else" ).

If using VBA then, of course, you can have a single formula returning one of n values and then have the cell background set accordingly.

shabbs wrote:

LeKing wrote:Cmon Big Bad Ev, It's time to learn VBA you're gonna need it later if you want the best jobs  

COBOL baby, COBOL.

You mean use COBOL instead of BASIC in VBA? Like VCA?  

Thanks for the help, it worked.

Now I have this formula and I'm lost lol

For example,

I have $100,000 on March 31, 2014. I am spending $10,000 per month. I want to know how many months I have until I run out of funds (from today's date).

Any advice?

This feels like we are doing your homework...

 

lol that's the last thing I need. I'm confused as hell

We're a community here!!!

OK, I have most of the formula. Just this one final part:

This makes no sense to me. I subtract 03/31/2014 from today's date (to get the month numbers) and I get -9. WTF?

I put in =MONTH(cell)-MONTH(NOW)...what am I doing wrong?

I want the number to come up as 3 or 4 or whatever the number of months is until March 31.

nevermind i got it now

Still having issues with conditional formatting but I get the gist of it

Ev wrote:OK, I have most of the formula. Just this one final part:

This makes no sense to me. I subtract 03/31/2014 from today's date (to get the month numbers) and I get -9. WTF?

I put in =MONTH(cell)-MONTH(NOW)...what am I doing wrong?

I want the number to come up as 3 or 4 or whatever the number of months is until March 31.

So what did you do wrong, other than missing the "()" after "NOW")? One thing I would suggest is using today() instead of now() for dates.

I used another formula altogether:

=(future date-TODAY()+1)/30)

Ev wrote:I used another formula altogether:

=(future date-TODAY()+1)/30)

Why? What you need is how many months have passed. You probably ignored the year.

=((year(future)-year(today))*12)+(month(future)-month(today())))

No I don't need how many months have passed,

The formula works, I just did it.

You put in March 31, 2013 as future date, - today, and it gives you 4. That's correct.

wait i migth be confused lol, just a sec

Ah ok I forgot to say that I already got the first part done (the one you just wrote). But then I needed the amount of months from now until March 31, whcih I added. It's all good now.