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]

16Excel Conditional Formatting - Page 2 Empty 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:
Excel Conditional Formatting - Page 2 Captur15

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.

17Excel Conditional Formatting - Page 2 Empty 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.

18Excel Conditional Formatting - Page 2 Empty Re: Excel Conditional Formatting on Sat Nov 30, 2013 9:30 am

TheAvatar


Veteran
Veteran
I'm the king of excel Smile

19Excel Conditional Formatting - Page 2 Empty Re: Excel Conditional Formatting on Sat Nov 30, 2013 12:59 pm

LeCaptain

LeCaptain
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 

20Excel Conditional Formatting - Page 2 Empty Re: Excel Conditional Formatting on Sat Nov 30, 2013 3:53 pm

shabbs

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

21Excel Conditional Formatting - Page 2 Empty Re: Excel Conditional Formatting on Sat Nov 30, 2013 10:13 pm

wprager

wprager
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

22Excel Conditional Formatting - Page 2 Empty Re: Excel Conditional Formatting on Sat Nov 30, 2013 10:16 pm

wprager

wprager
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

23Excel Conditional Formatting - Page 2 Empty Re: Excel Conditional Formatting on Mon Dec 02, 2013 10:20 am

Ev

Ev
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?

24Excel Conditional Formatting - Page 2 Empty Re: Excel Conditional Formatting on Mon Dec 02, 2013 11:29 am

shabbs

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

Sarcasm 

25Excel Conditional Formatting - Page 2 Empty Re: Excel Conditional Formatting on Mon Dec 02, 2013 11:32 am

Ev

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

We're a community here!!!

26Excel Conditional Formatting - Page 2 Empty Re: Excel Conditional Formatting on Mon Dec 02, 2013 12:06 pm

Ev

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

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

Ev

Ev
Franchise Player
Franchise Player
nevermind i got it now

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

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

wprager

wprager
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

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

Ev

Ev
Franchise Player
Franchise Player
I used another formula altogether:

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

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

wprager

wprager
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

31Excel Conditional Formatting - Page 2 Empty Re: Excel Conditional Formatting on Mon Dec 02, 2013 3:08 pm

Ev

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

32Excel Conditional Formatting - Page 2 Empty Re: Excel Conditional Formatting on Mon Dec 02, 2013 3:11 pm

Ev

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

33Excel Conditional Formatting - Page 2 Empty Re: Excel Conditional Formatting on Mon Dec 02, 2013 3:14 pm

Ev

Ev
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