You are not connected. Please login or register

Excel Conditional Formatting

Go to page : Previous  1, 2, 3  Next

Go down  Message [Page 2 of 3]

16 Re: Excel Conditional Formatting on Fri Nov 29, 2013 4:35 pm

wprager


Administrator
Administrator
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.

17 Re: Excel Conditional Formatting on Fri Nov 29, 2013 5:40 pm

shabbs


Hall of Famer
Hall of Famer
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.

18 Re: Excel Conditional Formatting on Sat Nov 30, 2013 9:30 am

TheAvatar


Veteran
Veteran
I'm the king of excel Smile

19 Re: Excel Conditional Formatting on Sat Nov 30, 2013 12:59 pm

LeCaptain

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

20 Re: Excel Conditional Formatting on Sat Nov 30, 2013 3:53 pm

shabbs

avatar
Hall of Famer
Hall of Famer
LeKing wrote:Cmon Big Bad Ev, It's time to learn VBA you're gonna need it later if you want the best jobs Cool 
COBOL baby, COBOL.

21 Re: Excel Conditional Formatting on Sat Nov 30, 2013 10:13 pm

wprager

avatar
Administrator
Administrator
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.


_________________
Hey, I don't have all the answers. In life, to be honest, I've failed as much as I have succeeded. But I love my wife. I love my life. And I wish you my kind of success.
- Dicky Fox

22 Re: Excel Conditional Formatting on Sat Nov 30, 2013 10:16 pm

wprager

avatar
Administrator
Administrator
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 Cool 
COBOL baby, COBOL.
You mean use COBOL instead of BASIC in VBA? Like VCA? Puking2 


_________________
Hey, I don't have all the answers. In life, to be honest, I've failed as much as I have succeeded. But I love my wife. I love my life. And I wish you my kind of success.
- Dicky Fox

23 Re: Excel Conditional Formatting on Mon Dec 02, 2013 10:20 am

Ev

avatar
Franchise Player
Franchise Player
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?

24 Re: Excel Conditional Formatting on Mon Dec 02, 2013 11:29 am

shabbs

avatar
Hall of Famer
Hall of Famer
This feels like we are doing your homework...

Sarcasm 

25 Re: Excel Conditional Formatting on Mon Dec 02, 2013 11:32 am

Ev

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

We're a community here!!!

26 Re: Excel Conditional Formatting on Mon Dec 02, 2013 12:06 pm

Ev

avatar
Franchise Player
Franchise Player
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.

27 Re: Excel Conditional Formatting on Mon Dec 02, 2013 2:22 pm

Ev

avatar
Franchise Player
Franchise Player
nevermind i got it now

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

28 Re: Excel Conditional Formatting on Mon Dec 02, 2013 2:32 pm

wprager

avatar
Administrator
Administrator
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.


_________________
Hey, I don't have all the answers. In life, to be honest, I've failed as much as I have succeeded. But I love my wife. I love my life. And I wish you my kind of success.
- Dicky Fox

29 Re: Excel Conditional Formatting on Mon Dec 02, 2013 2:34 pm

Ev

avatar
Franchise Player
Franchise Player
I used another formula altogether:

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

30 Re: Excel Conditional Formatting on Mon Dec 02, 2013 2:46 pm

wprager

avatar
Administrator
Administrator
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())))


_________________
Hey, I don't have all the answers. In life, to be honest, I've failed as much as I have succeeded. But I love my wife. I love my life. And I wish you my kind of success.
- Dicky Fox

31 Re: Excel Conditional Formatting on Mon Dec 02, 2013 3:08 pm

Ev

avatar
Franchise Player
Franchise Player
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.

32 Re: Excel Conditional Formatting on Mon Dec 02, 2013 3:11 pm

Ev

avatar
Franchise Player
Franchise Player
wait i migth be confused lol, just a sec

33 Re: Excel Conditional Formatting on Mon Dec 02, 2013 3:14 pm

Ev

avatar
Franchise Player
Franchise Player
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.

Sponsored content


Back to top  Message [Page 2 of 3]

Go to page : Previous  1, 2, 3  Next

Permissions in this forum:
You cannot reply to topics in this forum